Excel VBA - apply conditional formatting formula to range

Question

I need to highlight the cells in a range MyRange (i.e. B2:B30), where the value of the cell is <= 0 OR > 30). With macro recorder, applying conditional formatting to a single cell, I got this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OR(O5<=0,O5>30)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False

Is there a way to apply the formula to each cell of MyRange?

Answer 1

Something like this? The range is hardcoded here, do you need it to be dynamic?

Sub test()
    Dim myRange As Range
    Set myRange = Range("B2:B30")
    
    With myRange
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(O5<=0,O5>30)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.599963377788629
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

Answer 2

You can also use the out-of-the-box FormatConditions (xlLessEqual & xlGreater) to slightly simplify without the need for formula. This is also more intuitive to read IMO

Sub Example()

'Declare & set variables
Dim ws As Worksheet, Target As Range
Set ws = ThisWorkbook.Sheets("Sheet2")
Set Target = ws.Range("B2:B30")

    'Delete existing formats, add new rules, apply formats
    With Target
        .FormatConditions.Delete
        .FormatConditions.Add xlCellValue, xlLessEqual, 0
        .FormatConditions.Add xlCellValue, xlGreater, 30
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
        .FormatConditions(2).Interior.ThemeColor = xlThemeColorAccent1
    End With 

End Sub

Answer 3

To apply the conditional formatting to B2:B30.

With Range("B2:B30")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, _
        Formula1:="=AND(" & .Cells(1, 1).Address(0, 0) & "<>"""",OR(" & .Cells(1, 1).Address(0, 0) & "<=0," & .Cells(1, 1).Address(0, 0) & ">30))"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599963377788629
    End With
    .FormatConditions(1).StopIfTrue = False
End With