Create Heatmap with Excel VBA

Creating a heatmap in Excel using VBA involves several steps, such as defining the range, setting up conditional formatting rules, and applying color scales based on cell values. Here’s a detailed guide with the code and explanations:

VBA Code for Creating a Heatmap

Sub CreateHeatmap()
    ' Declare variables
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim minVal As Double, maxVal As Double
    Dim cell As Range   
    ' Set the target worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change to your sheet name
    ' Define the data range for the heatmap (e.g., A1 to D10)
    Set dataRange = ws.Range("A1:D10")  ' Adjust the range as needed
    ' Find the minimum and maximum values in the range
    minVal = Application.WorksheetFunction.Min(dataRange)
    maxVal = Application.WorksheetFunction.Max(dataRange)   
    ' Clear any existing formatting
    dataRange.FormatConditions.Delete   
    ' Apply a color scale to the data range (Heatmap)
    With dataRange.FormatConditions.AddColorScale(3)
        ' Apply a 3-color scale
        With .ColorScaleCriteria(1)
            .Type = xlConditionValueNumber
            .Value = minVal
            .FormatColor.Color = RGB(255, 255, 255)  ' White (low value)
        End With
        With .ColorScaleCriteria(2)
            .Type = xlConditionValueNumber
            .Value = (maxVal + minVal) / 2
            .FormatColor.Color = RGB(255, 255, 0)  ' Yellow (mid value)
        End With
        With .ColorScaleCriteria(3)
            .Type = xlConditionValueNumber
            .Value = maxVal
            .FormatColor.Color = RGB(0, 255, 0)  ' Green (high value)
        End With
    End With
End Sub

Explanation

1. Variables:
   • ws: This is the variable that represents the worksheet where the heatmap will be applied.
   • dataRange: This defines the range of cells that you want to apply the heatmap to (e.g., A1:D10).
   • minVal and maxVal: These variables store the minimum and maximum values found within the selected data range.
2. Setting the Worksheet and Data Range:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): This sets the worksheet you’re working on. Modify « Sheet1 » to your desired sheet’s name.
   • Set dataRange = ws.Range(« A1:D10 »): This defines the range where the heatmap will be applied. You can change the range to whatever fits your needs.
3. Finding Min and Max Values:
   • minVal = Application.WorksheetFunction.Min(dataRange): This calculates the minimum value within the selected data range.
   • maxVal = Application.WorksheetFunction.Max(dataRange): Similarly, this calculates the maximum value within the data range.
4. Clearing Existing Formatting:
   • dataRange.FormatConditions.Delete: This line removes any existing conditional formatting from the range, so the new heatmap can be applied without interference.
5. Applying Conditional Formatting (Color Scale):
   • With dataRange.FormatConditions.AddColorScale(3): This starts the conditional formatting rule for a 3-color scale.
   • The .ColorScaleCriteria(n) represents the 3 points in the color scale: low, medium, and high values.
     • .ColorScaleCriteria(1): Defines the color for the lowest value (minVal). It is set to white (RGB(255, 255, 255)).
     • .ColorScaleCriteria(2): Defines the middle value, which is the average of the minimum and maximum values. This is set to yellow (RGB(255, 255, 0)).
     • .ColorScaleCriteria(3): Defines the color for the highest value (maxVal). It is set to green (RGB(0, 255, 0)).
6. Running the Code:
   • When you run the CreateHeatmap subroutine, the cells in the specified range will be color-coded based on their values, with the lowest values being white, middle values yellow, and the highest values green, creating a heatmap effect.

Customizing the Code

• Adjust Range: Modify the Set dataRange = ws.Range(« A1:D10 ») to target a different range, such as a larger dataset.
• Color Customization: You can change the RGB values to customize the colors. For example, you can use RGB(255, 0, 0) for red or RGB(0, 0, 255) for blue.
• Scale Type: If you want to use a 2-color scale instead of a 3-color scale, you can change the AddColorScale(3) to AddColorScale(2) and define two color criteria.