Conditional Formatting is a useful tool in Excel that allows
you to do things like highlight duplicate cells, or color every other row in
with color, and so on. If you have a large range or table with many conditional
formatting rules, sometimes things can get a little messy. If you’re inserting,
adding, or deleting rows and columns often, your conditional formatting rules
might go from a short, highly understandable list, to a complete cluster:
One way to be able to reset your conditional formatting
rules is with a macro. We’re going to use a macro to automatically delete the
conditional formatting and then add it back.
First, to clear and delete all the conditional formatting
from a sheet with a macro, use this code, changing the A:AQ with whatever range
you’re using:
'--------delete the conditional formatting--------
With ActiveSheet.Range("A:AQ")
.FormatConditions.Delete
End With
Now it’s time to add conditional formatting with a macro. In
this example, I have a status column C where I enter values, and based on these
values the format of my range will change.
Use LastRow to find the last row of data, making it a
dynamic range (meaning the size of the range changes based on how much data is
inside the range).
'define the last row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
First, define the range where you want to apply the
formatting.
Next, define the formula or rule. Here, I want if the value
in Column C is the letter N to change the font color to red. I use double
quotations to have a quotation. Notice there is only one $ sign. If I put $C$11
then the formula would not trickle down through the rest of the range.
Finally, define the condition, font color to red the color
index is 3. See the font color index here: https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/
This is the first rule I am adding so notice the (1) inside
the parenthesis.
'-------add the conditional formatting-------
'if new, change font to red
With
ActiveSheet.Range("D11:AP" & LastRow)
.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""N"""
.FormatConditions(1).Font.ColorIndex = 3
End With
Now I want to add another conditional formatting rule
programmatically. This time, change the font color to green if there is an H in
the column C. Green uses a 4 in the color index. This is the 2nd
rule so notice the (2).
'if h, change font to green
With
ActiveSheet.Range("D11:AP" & LastRow)
.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""h"""
.FormatConditions(2).Font.ColorIndex = 4
End With
Finally, if there is an X in column C, I want to use
strikethrough to cross out the words.
'if x, then cross-out
With
ActiveSheet.Range("D11:AP" & LastRow)
.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""x"""
.FormatConditions(3).Font.Strikethrough = True
End With
Here's what the conditional formatting rules look like after running the macro:
And that’s how you add conditional formatting with a macro. Let me know in the comments below if you have any questions.
