Software Lab Conditional Format
Description:
This set of VBA codes can be used to format all cells in a range that meet one or multiple criteria. This was particularly useful when you wished to highlight all cells that meet certain condition.
Code:
Sub LoopCF()

    Dim Rng     As Range
    Dim rnArea  As Range
    Dim CRng    As Range
    Dim doc     As String
    Dim ic      As Long
    Dim rnCell  As Range
    
    Set rnArea = Range("C3:J6, C9:J12, C15:J18, C21:J24")
    Set CRng = Range("L40:L53")
    
    rnArea.Interior.ColorIndex = xlNone
    
    For Each cell In CRng
        doc = "*" & cell.Value & "*"
        For Each rnCell In rnArea
            With rnCell
                If .Value <> 0 Then
                    Select Case True
                        Case rnCell Like doc
                        .Interior.Color = 13551615
                    End Select
                    Else
                    ic = xlNone
                End If
            End With
        Next rnCell
    Next cell
       
End Sub

Code:
Sub ConditionalFormatting()


Range("C11").Select

    With Range("C11:C16").FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=C11<=C$4")
        
        .Interior.Color = RGB(198, 239, 206)
        .Font.Color = RGB(0, 97, 0)
    
    End With
End Sub

Sub ClearFormatting()

rspn = MsgBox("To clear all formatting", vbYesNo)
If rspn = vbYes Then Range("C9:Z140").FormatConditions.Delete

End Sub

Procedures:
  1. Insert a new module (VBA) in your excel file.
  2. Copy and paste the above codes into the module.
  3. The CRng variable can be re-defined to house your criteria.
  4. Go to active worksheet where you wish to apply this conditional formatting and run the macro.

Leave a Reply

Your email address will not be published. Required fields are marked *