Excel VBA Selecting Multiple columns with last row and apply Condition Format

Question

Please assist me with the following question. I'm trying to apply Condition Format to many columns with the final row selected, but it's not working the way I want it to.

Working One:

Dim lr As Long

With Range("E:E,F:F,H:H")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

This one is not working :

Dim lr As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range(""E2:E" & lr,"F2:F" & lr,"H2:H" & lr)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

And I'm trying to apply color to a cell that contains error "#N/A" but it is not working.

Dim lr As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range(""E2:E" & lr,"F2:F" & lr,"H2:H" & lr)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=#N/A"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

Any help with one above 2 queries would be appreciated.

Kithuzzz · Answer 1 · Mar 21, 2023

Try this:

Sub Test()
    Dim lr As Long, ws As Worksheet
    
    Set ws = ActiveSheet 'for example
    lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    With ws.Range("E2:E" & lr & ",F2:F" & lr & ",H2:H" & lr)
        With .FormatConditions.Add(Type:=xlCellValue, _
                                   Operator:=xlEqual, Formula1:="=0")
            .SetFirstPriority
            .Interior.Color = 13561798
            .StopIfTrue = False
        End With
    End With
End Sub