Software Lab Conditional Format
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.
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
                    ic = xlNone
                End If
            End With
        Next rnCell
    Next cell
End Sub

Sub ConditionalFormatting()


    With Range("C11:C16").FormatConditions.Add( _
        Type:=xlExpression, _
        .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

  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 *