CODEWITHSUNDEEP
excelvba
Chewy1775
Chewy1775

Reputation: 13

Using conditional format in VBA

I am using a loop to put a conditional format in "every 4th row" in "column D". However, I am not able to get the code correct.

I had two seperate things happen. First, I have had the formula show up in the spreadsheet with " " around the formula and the conditional format did not work. So now I am trying to rewrite it using the following code and it tells me compile error expected end of statement.

Any help on how I can get the conditional format of D(i-1)>sum(D(i):D(i+2) shade cell red to work is appreciated.

This is the middle of the For/Next loop where I am trying to shade.

    Range("D" & (i - 1)).Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="D" & (i-1) & "> Sum(D" & i & "D" & (i + 2)")"

        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

        i = i + 4

Upvotes: 1

Views: 939

Answers (2)

Tim Williams
Tim Williams

Reputation: 166825

This worked for me:

Sub Tester()

    Dim i As Long, fc As FormatCondition

    For i = 2 To 10 Step 4

        Set fc = ActiveSheet.Range("D" & (i - 1)).FormatConditions. _
                       Add(Type:=xlExpression, _
                       Formula1:="=D" & (i - 1) & "> Sum(D" & i & ":D" & (i + 2) & ")")

        fc.SetFirstPriority

        With fc.Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With

        fc.StopIfTrue = False

    Next i

End Sub

Upvotes: 1

Joost
Joost

Reputation: 1216

Is it a requirement to do this in VBA? I'm asking because, through regular conditional formatting, you could also easily apply it to every 4th row.

To do so, you simply need a formula like this one in your conditional formatting:

=AND(MOD(ROW($D5),4)=0, $D5 > SUM($D$2:$D4))

The MOD-function divides the row number by 4 and checks if the remainder is 4.

It's not exactly what you asked for, but it might solve your situation...

Upvotes: 0

Related Questions