Help with adding a loop to a Macro

[Frank3923]

I have recorded a macro that will add conditional formatting to 2 columns based on the values in 2 other columns. How do I

go about adding a loop, that would be based on Column "A" containing a value. (IF column “A” contains a value, put

conditional format into Columns “B” and “C” cells of that row). Another question I have regarding the recorded macro is, can

the selection.Borders be combined? or does each one have to have its own "With" and "End With"?

Thank you in advance

Sub Cond_Format()
'
' Cond_Format Macro
'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D2>E2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Color = -16751104
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("C2").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=""AND(E2>D2)"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -10477568
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10092543
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Sheet1

[HTML removed]

 

[Von Pookie]

Frank,
There was something weird going on with the HTML example you posted; I had to remove it. Please feel free to try posting it again.

 

[Frank3923]

Below is an example of what I am trying to do with a vba Loop

Excel Workbook
	A	B	C	D	E
1	Day	Blue Group	Red Group	Blue Results	Red Results
2	Mon	Team-1	Team-2	13	14
3	Tues	Team-3	Team-4	8	4
4	Wed	Team-5	Team-6	3	9
5	Thurs	Team-7	Team-8	12	5
6	Fri	Team-9	Team-10	6	7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(D2>E2)	Abc
C2	1. / Formula is =AND(E2>D2)	Abc
B6	1. / Formula is =AND(D6>E6)	Abc

 

[Peter_SSs]

Frank

I'm not sure why you are using the AND() function in your Conditional Formatting formulas? AND is used to test 2 or more conditions, but you only have 1.

I don't think you need a loop. You should be able to apply the CF to all the cells ina column at once.

See if this does what you want. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Cond_Format_2()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 1)<br>            .FormatConditions.Delete<br>            .FormatConditions.Add Type:=xlExpression, _<br>                Formula1:="=D2>E2"<br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Font<br>                .Bold = <SPAN style="color:#00007F">True</SPAN><br>                .Color = -16751104<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Borders<br>                .LineStyle = xlContinuous<br>                .Weight = xlThin<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Interior<br>                .ThemeColor = xlThemeColorAccent3<br>                .TintAndShade = 0.599963377788629<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .FormatConditions(1).StopIfTrue = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 2)<br>            .FormatConditions.Delete<br>            .FormatConditions.Add Type:=xlExpression, _<br>                Formula1:="=E2>D2"<br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Font<br>                .Bold = <SPAN style="color:#00007F">True</SPAN><br>                .Color = -10477568<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Borders<br>                .LineStyle = xlContinuous<br>                .Weight = xlThin<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Interior<br>                .PatternColorIndex = xlAutomatic<br>                .Color = 10092543<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .FormatConditions(1).StopIfTrue = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

Result:

Excel Workbook
	A	B	C	D	E
1	Day	Blue Group	Red Group	Blue Results	Red Results
2	Mon	Team-1	Team-2	13	14
3	Tues	Team-3	Team-4	8	4
4	Wed	Team-5	Team-6	3	9
5	Thurs	Team-7	Team-8	12	5
6	Fri	Team-9	Team-10	6	7
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =D2>E2	Abc
C2	1. / Formula is =E2>D2	Abc

 

[Frank3923]

Thank you Peter, I will give this a try as you suggested.

Frank

 

[Frank3923]

Peter:
Here are the results for the testing of macro, that you provided.

When I insert the code, and run it, all cells in column "B" turn to the Conditional format.

When checking the "Conditional Formatting Rules Manager", it shows the following, for Cell "B2"

...Rule for formula is showing ( formula: =E3>F3,

...the formula in the macro reflected, Formula1:="=D2>E2"

...the format portion of the rules manager is correct, as well as the "Applies to column, for the range.

in checking each cell, Column "B" in the "Rules manager" the formula remains the same. - formula: =E3>F3,

and is not incrementing as needed.

When Checking column "C", and the first cell being "C-2", Rule for formula is showing ( formula: =G3>F3.)

the formula in the macro reflected, the formula should be, Formula1:="=E2>D2". In checking the remaining

cells in Column "C" the formula remains the same in all cells being checked. ( formula: =G3>F3.), so

those cells as well are not incrementing by row as needed.

the reasoning behind my initial request for the loop, was for the purpose of incrementing the formula, that is being used for determining the conditional formating of each row.

Thank you in advance.

 

[Peter_SSs]

Excel 2007?

The fact that the formula in Rules Manager appears the same in each cell is not a problem since the formula refers to the first cell in the 'Applies to' range. If we get the CF applied correctly, the formula will show D2>E2 which ever cell in column B you check because the 'Applies to' range first cell is B2. So I still believe a loop is not necessary.

Each of the 2 main blocks in the code start with

With .Offset(,n)
.FormatConditions.Delete

Between these 2 lines in each block add a new line:
.Select

and try the code again.

 

[Frank3923]

Thank you Peter, that did the trick