Using conditional format in VBA

Question

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

`Formula1:="D" & (i-1) & "> Sum(D" & i & "D" & (i + 2)")"` missing an & at the end there: `... & (i + 2) & ")"` — Tim Williams, Jan 08 '14 at 23:48
Thank you. I missed that. I also noticed I missed the ":" in the sum function. Now when I run the code, the conditional format rule exists as ="D4>Sum(D5:D7)" but it does not actually do anything. The preview in the manage rule box shows red like I want, but "D4" does highlight and it definitly greater than the sum of the next three cells. — Chewy1775, Jan 09 '14 at 22:54

score 1 · Accepted Answer · answered Jan 09 '14 at 23:12

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

This got me to where I needed to be. Thank you. Now I am just going to throw in a clear format statement so each time I run this code it does not create additional format rules. — Chewy1775, Jan 10 '14 at 17:53

score 0 · Answer 2 · answered Jan 09 '14 at 00:44

0

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...

answered Jan 09 '14 at 00:44

Joost

1,126
1
10
19

I plugged the mod formula in, but it only worked for the one cell. The reason I was doing it in VBA is because I need it to happen in every 4th cell for a variable number of cells. I have 175 parts now, but that could be 125 or 225 next month. I have coded the sub routines to add new parts and sort them. Now I just need to highlight this one cell if it is greater than the sum of the three that follow it. – Chewy1775 Jan 09 '14 at 22:47
I highlighted the last row and named it and did a conditional format from the first row to the named row. It worked, however, after I add more rows to the middle of that range, the new rows do not have that conditional format. – Chewy1775 Jan 10 '14 at 00:01
Indeed, it works like you say (with my formula in the conditional formatting) but gets messed up when you insert rows. This is very poor design of Excel if you ask me - instead of extending the range that the formatting applies to, Excel makes all sorts of weird assumptions on the conditional format range. I'm reading up to see if this can be changed - keep you posted. – Joost Jan 12 '14 at 22:20
It's the same issue as reported here, but I haven't found a workaround yet. Even when using named ranges, conditional formatting gets messed up. http://www.excelforum.com/excel-formulas-and-functions/661589-conditional-formating-adding-and-deleting-rows.html – Joost Jan 12 '14 at 23:12
OK... so far, I've found these sources, all of them complaining about the same behaviour, but none of them offering a solution... http://www.excelforum.com/excel-programming-vba-macros/778585-inserting-new-rows-splits-conditional-format.html http://www.excelforum.com/excel-formulas-and-functions/661589-conditional-formating-adding-and-deleting-rows.html http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/ http://stackoverflow.com/questions/12480934/ http://stackoverflow.com/questions/20689702/ – Joost Jan 12 '14 at 23:32

Using conditional format in VBA

2 Answers2