Create dynamic conditional formatting in Excel using VBA.

Goal:

We want to apply dynamic conditional formatting using VBA, so that the format changes automatically based on cell values. This can be useful, for example, when you want to color code cells based on specific criteria like greater than, less than, between, etc.

Step-by-Step Guide:

1. Understanding Conditional Formatting in Excel VBA

Conditional formatting allows you to automatically format cells based on specific conditions (e.g., changing the cell color if the value exceeds a certain number). The VBA approach allows for dynamic application of these formats based on changing data.

2. Preparing the Worksheet

Let’s assume you have a range of data (e.g., A1:A10) and you want to apply conditional formatting to highlight the cells that meet specific criteria.

3. Writing the VBA Code

Below is a detailed VBA code to create dynamic conditional formatting for the range A1:A10. The code applies formatting based on the following conditions:

• Cells that are greater than 50 will be highlighted in green.
• Cells that are less than 20 will be highlighted in red.
• Cells that are between 20 and 50 will be highlighted in yellow.

VBA Code:

Sub CreateDynamicConditionalFormatting()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cf As FormatCondition
    ' Set the target worksheet and rang
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A10")
    ' Clear any existing conditional formatting
    rng.FormatConditions.Delete
    ' 1. Apply formatting for cells greater than 50
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="50")
    cf.Interior.Color = RGB(0, 255, 0) ' Green color
    ' 2. Apply formatting for cells less than 20
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="20")
    cf.Interior.Color = RGB(255, 0, 0) ' Red color
    ' 3. Apply formatting for cells between 20 and 50
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="20", Formula2:="50")
    cf.Interior.Color = RGB(255, 255, 0) ' Yellow color
    MsgBox "Dynamic Conditional Formatting applied successfully!"
End Sub

Explanation of the Code

• Set ws and rng: We specify the worksheet (ws) and the range (rng) to apply the conditional formatting. In this case, we’re working with « Sheet1 » and the range A1:A10.
• Clear Existing Formatting: The line rng.FormatConditions.Delete ensures that any pre-existing conditional formatting on the range is cleared before applying new rules.
• Adding Format Conditions: For each condition (greater than, less than, and between), we use FormatConditions.Add. Here’s a breakdown of the method:
  • Type:=xlCellValue: We’re applying the condition to cell values.
  • Operator:=xlGreater, xlLess, xlBetween: Specifies the type of condition (greater than, less than, between).
  • Formula1 and Formula2: These are the values we compare against. For example, in the case of xlGreater, Formula1 is set to « 50 », meaning cells greater than 50 will be formatted.
• Formatting the Cells: The cf.Interior.Color = RGB(r, g, b) line sets the background color of the cells that meet the condition. In the example:
  • Green (0, 255, 0) for values greater than 50.
  • Red (255, 0, 0) for values less than 20.
  • Yellow (255, 255, 0) for values between 20 and 50.

4.Running the Code

To run the code:

• Open the workbook where you want to apply conditional formatting.
• Press Alt + F11 to open the VBA editor.
• Insert a new module: Insert > Module.
• Paste the code into the module.
• Press F5 or run the CreateDynamicConditionalFormatting macro from the « Run » menu.

5.Modifying for Dynamic Changes

You can adjust the conditions dynamically by linking them to cell values. For example, if you want the condition to depend on a value in a specific cell (say, B1), you can modify the formula as follows:

Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:= »=B1″)

This way, the formatting will change based on the value in B1.

Conclusion

This code demonstrates how to apply dynamic conditional formatting to a range of cells using VBA in Excel. You can modify the conditions and apply more complex formatting as needed. The power of VBA allows for even more advanced logic, such as using formulas or applying different types of formatting (fonts, borders, etc.) based on dynamic criteria.