How to Add Conditional Formatting with a Macro

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 2^nd 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.