Implement Advanced Conditional Formatting with Excel VBA

What is Conditional Formatting?

Conditional Formatting in Excel allows you to format cells based on certain criteria, such as cell values, formulas, or even the results of custom conditions. It’s useful for visually highlighting trends, anomalies, or important data points in large datasets. While Excel’s built-in conditional formatting options are intuitive, VBA gives you greater control and flexibility to apply more advanced rules and formats dynamically.

Advanced Conditional Formatting with VBA

The goal of this example is to demonstrate how to apply advanced conditional formatting rules using VBA. The following code highlights cells in a data range based on specific conditions, such as:

1. Highlight cells based on a value comparison (e.g., greater than a threshold).
2. Highlight duplicate values in a range.
3. Apply color scales to show values from low to high in a range.

Steps to Create VBA for Advanced Conditional Formatting

Let’s break down the VBA code step by step.

Step 1: Open the Visual Basic for Applications (VBA) Editor

1. Press Alt + F11 to open the VBA editor.
2. In the editor, insert a new module: Insert > Module.

Step 2: Sample VBA Code for Conditional Formatting

Here’s the VBA code that implements advanced conditional formatting:

Sub ApplyAdvancedConditionalFormatting()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Modify to your target sheet name
    ' Step 1: Clear any previous formatting
    ws.Cells.FormatConditions.Delete
    ' Step 2: Apply formatting for values greater than a threshold (e.g., 50)
    With ws.Range("A1:A20").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="50")
        .Interior.Color = RGB(255, 0, 0)  ' Red background for values greater than 50
        .Font.Color = RGB(255, 255, 255)  ' White text for contrast
        .Font.Bold = True
    End With
    ' Step 3: Apply formatting for duplicate values in a range
    With ws.Range("A1:A20").FormatConditions.AddUniqueValues
        .DupeUnique = xlDuplicate
        .Interior.Color = RGB(0, 255, 0)  ' Green background for duplicates
    End With
    ' Step 4: Apply a color scale (gradient) for values in a range
    With ws.Range("B1:B20").FormatConditions.AddColorScale(ColorScaleType:=3)
        ' First color - for lowest value
        .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
        .ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)  ' White
        ' Second color - for midpoint value
        .ColorScaleCriteria(2).Type = xlConditionValuePercentile
        .ColorScaleCriteria(2).Value = 50
        .ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)  ' Yellow
        ' Third color - for highest value
        .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
        .ColorScaleCriteria(3).FormatColor.Color = RGB(0, 255, 0)  ' Green
    End With
    ' Step 5: Apply a formula-based conditional format (e.g., highlight even numbers)
    With ws.Range("C1:C20").FormatConditions.Add(Type:=xlExpression, Formula1:="=MOD(C1,2)=0")
        .Interior.Color = RGB(0, 0, 255)  ' Blue background for even numbers
        .Font.Color = RGB(255, 255, 255)  ' White text for contrast
    End With
End Sub

Explanation of the Code

Let’s break down the various steps of the code:

1. Clear Any Previous Formatting

ws.Cells.FormatConditions.Delete

This line ensures that any existing conditional formatting is cleared before applying the new formatting. It ensures a clean slate before applying the new rules.

2. Highlight Values Greater Than a Threshold


This section creates a rule for the range A1:A20, where cells with values greater than 50 will have a red background (RGB(255, 0, 0)), white text (RGB(255, 255, 255)), and bold font. The Type:=xlCellValue specifies that the condition is based on the cell value, and Operator:=xlGreater defines the condition for values greater than the specified threshold.

3. Highlight Duplicate Values

With ws.Range("A1:A20").FormatConditions.AddUniqueValues
    .DupeUnique = xlDuplicate
    .Interior.Color = RGB(0, 255, 0)
End With

Here, the code applies a formatting rule to highlight duplicate values in the range A1:A20. The background color will be green (RGB(0, 255, 0)). This formatting helps identify repeated data quickly.

4. Apply Color Scale (Gradient) for Values

With ws.Range("B1:B20").FormatConditions.AddColorScale(ColorScaleType:=3)
    .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
    .ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)
    .ColorScaleCriteria(2).Type = xlConditionValuePercentile
    .ColorScaleCriteria(2).Value = 50
    .ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
    .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
    .ColorScaleCriteria(3).FormatColor.Color = RGB(0, 255, 0)
End With

This step applies a three-color gradient (Color Scale) to the range B1:B20. The lowest value will be formatted with white, the midpoint with yellow, and the highest value with green. The ColorScaleCriteria is used to define the different colors for the lowest, middle, and highest values.

5. Apply Formula-Based Conditional Formatting

With ws.Range("C1:C20").FormatConditions.Add(Type:=xlExpression, Formula1:="=MOD(C1,2)=0")
    .Interior.Color = RGB(0, 0, 255)
    .Font.Color = RGB(255, 255, 255)
End With

This rule highlights cells in C1:C20 that contain even numbers. The formula =MOD(C1,2)=0 checks if a cell’s value is divisible by 2 (i.e., if the number is even). The background color is set to blue (RGB(0, 0, 255)), and the font color is set to white for contrast.

Step 3: Run the VBA Macro

1. After pasting the code into the VBA editor, close the editor and return to Excel.
2. Press Alt + F8, select ApplyAdvancedConditionalFormatting, and click Run to apply the conditional formatting rules.

Conclusion

This VBA code demonstrates how to apply several types of advanced conditional formatting using VBA. You can modify the ranges, criteria, and formatting properties to suit your specific needs. By using VBA, you have much more control over complex formatting scenarios compared to the built-in Excel options.