Conditional formatting ignores empty cells with operator xlLess-CodePudding

I try to apply a conditional formatting on a range of cells in a loop.

my Range is:

Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K))

With it I want apply 2 conditions; I want color in red the cells when it greater than 180 or less than 100. I try this, and it works but all blanks, empty cells are also colored in red.

Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=100"

Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=180"

Notice that this range contains formulas and cells have color foreground and border (xlNone will delete this format)

CodePudding user response：

Use Type:=xlExpression. Here is an example for cell A1 which is the same as using

Range("A1").FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR(AND($A$1<100,TRIM($A$1)<>""),AND($A$1>180,TRIM($A$1)<>""))"

VBA example

Sub Sample()
    '~~> Values for demonstration purpose
    '~~> CF to be applied on cells A1:A4
    FirstLine = 1
    k = 1
    i = 5

    Dim FirstCell As String

    With Anomalies_sheet
        '~~> First cell in the range
        FirstCell = .Cells(FirstLine, k).Address(False, True)
        
        With .Range(.Cells(FirstLine, k), .Cells(i - 1, k))
            .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=OR(AND(" & FirstCell & "<100,TRIM(" & _
                      FirstCell & ")<>""""),AND(" & _
                      FirstCell & ">180,TRIM(" & FirstCell & ")<>""""))"
            '~~> Here I am setting the color to red
            '~~> Change as applicable
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
        End With
    End With