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

Question

Can someone help me on my below query. I'm trying to Select Multiple columns with last row and applying Condition Format but it is not working in the way I'm trying.

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 one above 2 queries would be appreciated..

Answer 1

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

FormatConditions.Add returns the just-added condition, so you can use that in a With block. Also fixed your Range() call.

For #N/A

With .FormatConditions.Add(Type:=xlExpression, Formula1:="=isNA(E2)")