VBA Conditional Formatting

What is Conditional Formatting in Excel VBA?

Conditional formatting in Excel VBA refers to defining rules that determine cell formatting based on certain criteria. With conditional formatting, you can create rules highlighting cells meeting specific conditions, such as numbers falling within a certain range or values above a particular threshold.

In Excel VBA, you can use the Conditional Formatting formula to apply formatting to cells or ranges. The FormatConditions collection represents a conditional format and can hold multiple format conditions. You can set the format conditions using methods and properties the FormatCondition object provides.

Consider the following example:

This example colors any cell value greater than 5 in red between the cell range A1-A10.

VBA Conditional Formatting Intro - Output

What is Conditional Formatting in Excel VBA?

Conditional formatting in Excel VBA allows you to apply formatting to cells based on specific criteria.
Conditional formatting can be applied to a cell or range of cells using the FormatConditions collection in VBA.
The FormatConditions collection represents a conditional format and can hold multiple format conditions.
You can define the rules for each conditional format using FormatConditions.Add method and specify the type of condition, operator, and criteria.
The format for each conditional format can be defined using properties such as Interior.Color or Font.Bold.
Conditional formatting can be applied based on various conditions, such as values greater than or equal to a certain threshold, duplicate values, or specific keywords.

How to use Conditional Formatting using VBA?

To use conditional formatting in Excel VBA, you can define a range of cells and then use the FormatConditions collection to add and configure the format conditions. To do that, follow the steps below:

Step 1: Go to the “Developer” tab in excel in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Consider an example where you need to highlight the green cells for those values greater than 10.

Step 2: Initialize a subroutine to highlight cells more significant than 10.

VBA Conditional Formatting - Use - Step 2

Step 3: Define the range of cells and a conditional format that formats the range.

VBA Conditional Formatting - Use - Step 3

Step 4: Set the range of cells to your preference.

VBA Conditional Formatting - Use - Step 4

Step 5: Delete any previously existing conditional format to avoid overlap.

VBA Conditional Formatting - Use - Step 5

Step 6: Set the conditions to color the cell green if the value exceeds 10.

VBA Conditional Formatting - Use - Step 6

Here, we define the type where the format is performed as xlCellValue, which means the condition will check the cell values.
The operator is defined as xlGreater since it means that Excel VBA needs to check for values greater than the parameter in Formula 1, “10.”
If there are any cell values greater than 10, that cell is colored Green.

Code:

Sub HighlightCells()
Dim rg As Range
Dim cond As FormatCondition
Set rg = Range(“A1:A20”)
rg.FormatConditions.Delete
Set cond = rg.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:=”10″)
cond.Interior.ColorIndex = 4
End Sub

Step 7: Run the code by pressing “F5” or the Run button in the toolbar. Then check the table.

VBA Conditional Formatting - Use - Step 7

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Let us look at some interesting examples of implementing conditional formatting in VBA.

Example #1

Suppose we have a table and must perform VBA Conditional Formatting multiple conditions in a single subroutine. Here, we highlight the cells with values greater than 7 in red. Also, we do the same for cells with a cell value less than 3 in blue.

Step 1: Initialize a subroutine to implement VBA Conditional Formatting with multiple conditions.

VBA Conditional Formatting - Example 1 - Step 1

Step 2: Set the range variable to store the cell range for the conditional formatting to work on. Set two conditions for VBA conditional formatting.

VBA Conditional Formatting - Example 1 - Step 2

Step 3: Assign the range in the range variable declared earlier to the cells A2-A20.

VBA Conditional Formatting - Example 1 - Step 3

Step 4: Remove any existing formatting conditions.

VBA Conditional Formatting - Example 1 - Step 4

Step 5: Initialize the first condition as shown:

VBA Conditional Formatting - Example 1 - Step 5

We define specific parameters such as:

Type – xlCellValue. It is made to check the numeric values of the table.
Operator – xlGreater. It checks for cells with values greater than the given value.
Formula1 – Used to define the condition. Here, the operator checks if a given cell value exceeds 7.
If a cell value greater than seven is found, it is colored red.

Step 6: Initialize the second condition.

VBA Conditional Formatting - Example 1 - Step 6

Here,

Type – xlCellValue. It is made to check the numeric values of the table.
Operator – xlLess. It checks for cells with values less than the given value.
Formula1 – Used to define the condition. Here, the operator checks if a given cell value is less than 3.
If a cell value lesser than three is found, it is colored blue.

Step 7: Run the code for the above table. The output is shown below:

VBA Conditional Formatting - Example 1 - Step 7

Code:

Sub ConditionalFormattingMultipleConditions()
Dim rg As Range
Dim cond1 As FormatCondition
Dim cond2 As FormatCondition
Set rg = Range(“A2:A20”)
rg.FormatConditions.Delete
Set cond1 = rg.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:=”7″)
With cond1.Interior
.Color = RGB(255, 0, 0)
End With
Set cond2 = rg.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:=”3″)
With cond2.Interior
.Color = RGB(0, 0, 255)
End With
End Sub

Example #2

Given a table where we need to highlight duplicate values and cells equal to the cell’s value in “C1.” It can be done by performing VBA Conditional Formatting with multiple conditions. We highlight the duplicate values in blue and change their font color to white. For the numbers equal to the number in cell “C1”, we highlight them in red.

Step 1: Develop a subroutine for VBA conditional formatting duplicate values and VBA Conditional Formatting based on another cell value.

VBA Conditional Formatting - Example 2 - Step 1

Step 2: Define a range of cells on which we perform the VBA Conditional formatting duplicate values condition. Define a condition to store the format condition parameters. To find duplicate values, we utilize a UniqueValues variable.

Step 3: Define the range of cells the conditional formatting will work on.

VBA Conditional Formatting - Example 2 - Step 3

Step 4: Remove any existing conditional formatting to avoid confusion.

Step 5: Set the first condition.

Here, we perform VBA conditional formatting based on another cell value, C1. We define its parameters as:

Type – xlCellValue. It is made to check the numeric values of the table.
Operator – xlEqual. It checks for cells with values equal to the given value.
Formula1 – Used to define the condition. Here, the operator checks if a given cell value equals the cell value in cell “C1”.
If a cell value equal to that in cell “C1” is found, it is colored red.

Step 6: Set the second condition to find unique values. By altering the DupeUnique property as “xlDuplicate,” we can find duplicate values in the table.

Step 7: Highlight the duplicate values by coloring the cell in blue, changing the font color to white, and bolding the letters.

Step 8: Print the above VBA code. Then, check the table to view the output.

Note: Here, the first condition overwrites the second condition. Thus, the condition of the duplicate value will not affect the cells highlighted in red.

Code:

Sub FindDuplicatesandHighlightEqual()
Dim rg As Range
Dim cond As FormatCondition
Dim uv As UniqueValues
Set rg = Range(“A1:A20”)
rg.FormatConditions.Delete
Set cond = rg.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:=”=$C$1″)
With cond.Interior
.Color = RGB(255, 0, 0)
End With
Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate
uv.Interior.Color = vbBlue
uv.Font.Color = vbWhite
uv.Font.Bold = True
End Sub

Important Things To Note

Use the FormatConditions collection to apply conditional formatting to a cell or range of cells.

Define the rules for each conditional format using FormatConditions.Add method and specify the type of condition, operator, and criteria.
Define the format for each conditional format using properties such as Interior.Color or Font.Bold.
Don’t use the Interior.Color property directly to obtain the color of a cell with conditional formatting.
Don’t use relative cell references in the VBA conditional formatting program, as it may cause the conditional formatting to be set incorrectly.
Don’t use VBA conditional formatting for distribution purposes, as it may cause potential Trust Center issues.

Frequently Asked Questions (FAQs)

1. How to do VBA conditional formatting if cell is not blank?

• To apply conditional formatting if a cell is not blank in Excel VBA, use the “Highlight Cell Rules” option or a formula to determine which cells to format.
• You can also use the AddUniqueValues method to identify duplicate values in a range and apply conditional formatting. To apply it based on another cell value, you can use the Formula1 parameter to reference the cell that contains the value to compare against.
• To apply conditional formatting to non-blank cells, you can use FormatConditions.Add method and specify the Formula1 parameter to reference the cell that contains the value to compare against.

2. How to do VBA conditional formatting greater than or equal to?

• To do VBA conditional formatting greater than or equal to, you can use FormatConditions.Add method and specify the Operator parameter as xlGreaterEqual to compare the values.
• Then, you can define the format for the conditional format using the Interior property of the FormatCondition object.

3. How to use conditional formatting to highlight blank cells in VBA?

• To use conditional formatting to highlight blank cells in VBA, you can use FormatConditions.Add method and specify the Operator parameter as xlEqual and the Formula1 parameter as “” to compare the values. Then, you can define the format for the conditional format using the Interior property of the FormatCondition object.
• Alternatively, you can use the “Highlight Cell Rules” option in the Conditional Formatting menu and select the “Blank Cells” option.

4. How to check color of cell conditional formatting in VBA?

• To check the color of a cell with conditional formatting in VBA, you can use DisplayFormat.Interior.Color property. This property allows you to retrieve the color of a cell that a rule has colored.
• However, using the Interior.Color property directly may not give you the color resulting from applied conditional formatting.
• Instead, you can use Range.DisplayFormat.Interior.Color to obtain the color of a cell with conditional formatting.

Download Template

This article must help understand VBA Conditional Formatting formulas and examples. We can download the template here to use it instantly.

VBA-Conditional-Formatting Download

VBA Conditional Formatting

What is Conditional Formatting in Excel VBA?

Table of contents

Key Takeaways

How to use Conditional Formatting using VBA?