Conditional Formatting Do Loop

Question

I've code I want to apply to about 1,200 plus rows. Works fine for the first row I want it to apply to, if AC2 >= 1 then the row 2:2 changed to red, I want to repeat if AC3 >=1 then 3:3 red and so on...

Sub Colour()

Rows("2:2").Select
Range("U2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AC$2>=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub

Way overcomplicated. Just use "=$AC2>=1" for the whole range (2:1200 or whatever you need). No need to apply separate conditional formattings for each row, just make sure you understand the $ signs and use them correctly. This will also be faster and easier to maintain. — vacip, Nov 12 '15 at 21:51
It isn't over complicated, what pnuts suggested wouldn't work as each row has a series of other formula that completes some sums based on if cells have certain criteria, only then if AC, AD, AE..... Etc is = to or greater than 1 then the row goes red because other cells in the row have done there sums which give a result that's more than 1. So this is really useful in saving time in applying the conditional formatting over 1000+ rows! — Mikey, Nov 14 '15 at 08:05
i don't see why it wouldn't work. Have you tried it? I can't think of a scenario where you need to reapply the same logic of conditional formatting to each row separately. Think about it, try to understand it, and folow @pnuts's steps. It should work. **try it!** — vacip, Nov 14 '15 at 11:00

score 1 · Answer 1 · answered Nov 13 '15 at 15:38

As mentioned by @vacip, VBA for this may be unnecessary. Select all the cells in your sheet and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=N($AC1)>=1

Format..., select choice of formatting, OK, OK.

This will apply to Row1 also but is the easiest way to set the range as the entire sheet. If AC1 contains a number equal to or greater than one and you don't want the formatting to apply to Row1 either adjust the Applies to range in Conditional Formatting Rules Manager or add another rule specifically for Row1 with whatever formatting (or none) you do want for it and ensure that rule has priority. Alternatively, expand the formula to something like:

=AND(ROW()<>1,N($AC1)>=1)

score 0 · Answer 2 · answered Nov 12 '15 at 20:50

Try this

    Sub Colour()

    Dim iCount As Integer
    For iCount = 2 To 4 Step 1
        Rows(iCount & ":" & iCount).Select
        Range("U" & iCount).Activate
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AC$" & iCount & ">=1"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    Next
End Sub

This will loop from 2 - 4 you can try more just by change 4 to the number you want ....

Conditional Formatting Do Loop

2 Answers2