You can use the following methods in VBA to apply conditional formatting to cells:
Method 1: Apply Conditional Formatting Based on One Condition
Sub ConditionalFormatOne()
Dim rg As Range
Dim cond As FormatCondition
'specify range to apply conditional formatting
Set rg = Range("B2:B11")
'clear any existing conditional formatting
rg.FormatConditions.Delete
'apply conditional formatting to any cell in range B2:B11 with value greater than 30
Set cond = rg.FormatConditions.Add(xlCellValue, xlGreater, "=30")
'define conditional formatting to use
With cond
.Interior.Color = vbGreen
.Font.Color = vbBlack
.Font.Bold = True
End With
End Sub
Method 2: Apply Conditional Formatting Based on Multiple Conditions
Sub ConditionalFormatMultiple()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
'specify range to apply conditional formatting
Set rg = Range("A2:A11")
'clear any existing conditional formatting
rg.FormatConditions.Delete
'specify rules for conditional formatting
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Mavericks")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Blazers")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Celtics")
'define conditional formatting to use
With cond1
.Interior.Color = vbBlue
.Font.Color = vbWhite
.Font.Italic = True
End With
With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
.Font.Bold = True
End With
With cond3
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With
End Sub
Method 3: Remove All Conditional Formatting Rules from Cells
Sub RemoveConditionalFormatting()
ActiveSheet.Cells.FormatConditions.Delete
End Sub
The following examples shows how to use each method in practice with the following dataset in Excel:
Example 1: Apply Conditional Formatting Based on One Condition
We can use the following macro to fill in cells in the range B2:B11 that have a value greater than 30 with a green background, black font and bold text style:
Sub ConditionalFormatOne()
Dim rg As Range
Dim cond As FormatCondition
'specify range to apply conditional formatting
Set rg = Range("B2:B11")
'clear any existing conditional formatting
rg.FormatConditions.Delete
'apply conditional formatting to any cell in range B2:B11 with value greater than 30
Set cond = rg.FormatConditions.Add(xlCellValue, xlGreater, "=30")
'define conditional formatting to use
With cond
.Interior.Color = vbGreen
.Font.Color = vbBlack
.Font.Bold = True
End With
End Sub
When we run this macro, we receive the following output:
Notice that each cell in the range B2:B11 that has a value greater than 30 has conditional formatting applied to it.
Any cell with a value equal to or less than 30 is simply left alone.
Example 2: Apply Conditional Formatting Based on Multiple Conditions
We can use the following macro to apply conditional formatting to the cells in the range A2:A11 based on their team name:
Sub ConditionalFormatMultiple()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
'specify range to apply conditional formatting
Set rg = Range("A2:A11")
'clear any existing conditional formatting
rg.FormatConditions.Delete
'specify rules for conditional formatting
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Mavericks")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Blazers")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Celtics")
'define conditional formatting to use
With cond1
.Interior.Color = vbBlue
.Font.Color = vbWhite
.Font.Italic = True
End With
With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
.Font.Bold = True
End With
With cond3
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With
End Sub
When we run this macro, we receive the following output:
Notice that cells with the team names “Mavericks”, “Blazers” and “Celtics” all have specific conditional formatting applied to them.
The one team with the name “Lakers” is left alone since we didn’t specify any conditional formatting rules for cells with this team name.
Example 3: Remove All Conditional Formatting Rules from Cells
Lastly, we can use the following macro to remove all conditional formatting rules from cells in the current sheet:
Sub RemoveConditionalFormatting()
ActiveSheet.Cells.FormatConditions.Delete
End Sub
When we run this macro, we receive the following output:
Notice that all conditional formatting has been removed from each of the cells.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: A Formula for “If” Cell Contains”
