For... Next Loop for Conditional Formatting

Question

I want to reset the conditional formatting as some of the ones I set manually have disappeared.

I've worked out how I can set the formatting conditions using VBA, but I've stuck on how to loop them for all the rows I need it to do.

This is one of the 12 conditions I need to set for each row...

Range("B17:J17").Select

        Selection.FormatConditions.Add Type:=xlExpression, _

            Formula1:="=$K$17=""E"""

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

        With Selection.FormatConditions(1).Interior

            .PatternColorIndex = xlAutomatic

            .Color = 10498160

            .TintAndShade = 0

        End With

        Selection.FormatConditions(1).StopIfTrue = False

I need to use a For... Next Loop to change the Row Numbers.

I need it to set the condition on this set of rows... B17:J17 and the Formula1... $K$17

Then Loop back and increase the Row Number by 1 to get... B18:J18 and the Formula1... $K$18

and so on until I have them all set from Row 17 to Row 216 inclusive.

What I've tried to do, but with it not working is...

        For RowNumber = 17 To 20 + 1

        CFRow = ("B" + RowNumber + ":J" + RowNumber)

        FormulaCell = ("$K$" + RowNumber)

        Range(CFRow).Select

        Selection.FormatConditions.Add Type:=xlExpression, _

            Formula1:=FormulaCell + "=""O"""

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

        With Selection.FormatConditions(1).Interior

            .PatternColorIndex = xlAutomatic

            .ThemeColor = xlThemeColorLight1

            .TintAndShade = 0.349986266670736

        End With

        Selection.FormatConditions(1).StopIfTrue = False

What I expected would happen was...

CFRow would equal "B17:J17"

FormulaCell would equal "$K$17"

Then after all 12 conditions I would have Next to send back to the top and repeat 1 number higher...

CFRow = "B18:J18"

FormulaCell = "$K$18"

and so on.

Any help to solve this would be a really big help.

Thank you in advance for your help and advice.

Neil

Answer 1

I understood your requirement.

Please take time to understand my answer.

You don't need to do a row at a time if you use relative row addressing you can do the whole lot at once.

Answer 2

Hi Bill,

I just want to make clear my problem.

I have a table, listing Programmes that I am recording for the week starting on Saturday from 6:00:00 am and finishing on Friday to 5:59:59 am.

I enter a code in "Column K" which I use the Conditional Formatting to Highlight the each Row.

I.e. Say I have the following.

____________________________________________________________________________________________________________

| Programme | Ch. No. | Channel Name | Day of Week | Date | Start Time | End Time | Duration | Code |

| Title             |              |                         |                       |          |                  |                 |                |           |

|--------------|----------|-----------------|----------------|-------|------------|-----------|-----------|--------|

| Prog. 1        |  101       | BBC1               |  Sat.               |          |                  |                |                |            |

|--------------|----------|-----------------|----------------|-------|------------|-----------|-----------|--------|

| Prog. 2        |  103       | ITV                  |  Sat.               |          |                  |                |                |            |

|--------------|----------|-----------------|----------------|-------|------------|-----------|-----------|--------|

ad so on...

So when I enter a code, it will only Highlight that Row, so if I enter "A" in the code, for Prog. 1, then Programme Title to Duration will be highlighted in Green.

And if I enter a code "Y" in Prog. 2, then Programme Title to Duration will be highlighted in Red.

I have a total of 12 codes, so I have created them, so what I want to do is a For... Next Loop, so it will create for each row the 12 Conditional Formatting, from Row 17 to Row 216.

So I want to get it to change the Row Number for each of the Rows.

So I need it to set the Row Number to go into the Range that the Conditional Formatting is set too,

so it needs to be for the first loop Bno:Jno

Also need to change where it looks for the code which is $K$no

So I need a line for each of the 12 Conditional Formatting which will do the following:

B + no = B17

J + no = J17

Range for Conditional Formatting = B17:J17

Set the Code to the following:

=$K$ + no + "=A"

Rule for conditional Formatting = =$K$17="A"

Then same numbers for the next Condition, once all 12 have been set, Repeat the Block of Conditional Formatting for 18, 19, 20, .... 216

I hope this is more clear.

Neil

Answer 3

If you make the row number relative rather than absolute (no $) then you can set the format for all the rows at once, e.g.

Range("B17:J216").Select

        Selection.FormatConditions.Add Type:=xlExpression, _

            Formula1:="=$K17=""E"""

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

        With Selection.FormatConditions(1).Interior

            .PatternColorIndex = xlAutomatic

            .Color = 10498160

            .TintAndShade = 0

        End With

        Selection.FormatConditions(1).StopIfTrue = False

or, to avoid the selection:

  With Range("B17:J216")

        **.**FormatConditions.Add Type:=xlExpression, _

            Formula1:="=$K17=""E"""

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

        With .FormatConditions(1).Interior

            .PatternColorIndex = xlAutomatic

            .Color = 10498160

            .TintAndShade = 0

        End With

        **.**FormatConditions(1).StopIfTrue = False

  End With

For interest, one reason your code with the loop wasn't working would be that

 FormulaCell = ("$K$" + RowNumber)

was missing the initial "="

 FormulaCell = "=$K$" + RowNumber