CODEWITHSUNDEEP
excelvba
Joe1990
Joe1990

Reputation: 23

Looping and offsetting for conditional formatting

I want my code to add conditional formatting to a range of cells.

It would highlight the entire row (I'm defining entire row from i5 to BH5 everything before it is just hidden things) red if the number in a certain cell is higher then a defined amount. It starts by checking $M5 (and checks all rows below if it is above the defined amount so M6, M7, M8,so on...), then I want it to move four columns to the right and to check $Q5.

If any of these cells are over the defined amount (we'll call it 10) then it will highlight the whole row red.

I figured I would use looping and offset as I don't want to have to type in $M5>10 $Q5>10 etc.

Sub QChelp()
    Dim colhl As Long
    Dim HLA As String
    HLA = "$M5"

    For colhl = 13 To 29 Step 4
        With Range("I5:BH300")
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
              "=OR(" & HLA & ">10)"
            With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 3
                    .TintAndShade = 0
                End With
            End With
        End With
    Next colhl

End Sub

Upvotes: 2

Views: 49

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

As commented, you are using a static reference...note that HLA never changes once you set it.

Instead, you can use the CELLS() reference:

... Formula1:="OR(" & cells(5, colhl).Address(RowAbsolute:=False) & ">10)"

(Also note that you don't need the starting = in a CF Formula in VBA)

Edit: Added .Address(...) per comments above.

Upvotes: 1

Related Questions