Help with adding a loop to a Macro

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
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

Code:
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]
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
Below is an example of what I am trying to do with a vba Loop

Excel Workbook
ABCDE
1DayBlue GroupRed GroupBlue ResultsRed Results
2MonTeam-1Team-21314
3TuesTeam-3Team-484
4WedTeam-5Team-639
5ThursTeam-7Team-8125
6FriTeam-9Team-1067
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B21. / Formula is =AND(D2>E2)Abc
C21. / Formula is =AND(E2>D2)Abc
B61. / Formula is =AND(D6>E6)Abc
 
Upvote 0
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
ABCDE
1DayBlue GroupRed GroupBlue ResultsRed Results
2MonTeam-1Team-21314
3TuesTeam-3Team-484
4WedTeam-5Team-639
5ThursTeam-7Team-8125
6FriTeam-9Team-1067
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B21. / Formula is =D2>E2Abc
C21. / Formula is =E2>D2Abc
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top