top of page

Use Case: Creating a Dynamic Heatmap in Excel Using VBA using Generative AI

Background

A quick visualization can often make large sets of numbers easier to understand. In this use case, we wanted to create a heatmap effect in an Excel range, where lower numbers are lighter and higher numbers are darker — automatically, using VBA.

The Prompt

In an Excel sheet, I have numbers in the range A1:K10. For every set of 10 numbers, I want a fill. Lower the numbers, lighter the fill color.
Think of a heatmap. I want a VBA script.

What Happened Next

  • ChatGPT understood the logic behind the request immediately —
    even though we never explicitly mentioned "conditional formatting" or "color scaling."

  • It generated a simple, effective VBA script that:

  • ✅ Calculates minimum and maximum values in the range
    ✅ Scales the color intensity dynamically
    ✅ Colors each cell lighter or darker based on the number value
    ✅ Ignores empty or non-numeric cells cleanly

The VBA Script

Sub CreateHeatmap()
    Dim rng As Range
    Dim cell As Range
    Dim minVal As Double
    Dim maxVal As Double
    Dim cellVal As Double
    Dim colorIntensity As Integer
    
    ' Define your range
    Set rng = Range("A1:K10")
    
    ' Find the minimum and maximum values in the range
    minVal = Application.WorksheetFunction.Min(rng)
    maxVal = Application.WorksheetFunction.Max(rng)
    
    ' Loop through each cell in the range
    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
            cellVal = cell.Value
            ' Scale color intensity between 255 (light) and 0 (dark)
            If maxVal <> minVal Then
                colorIntensity = 255 - Int((cellVal - minVal) / (maxVal - minVal) * 255)
            Else
                colorIntensity = 255
            End If
            ' Set cell color based on value
            cell.Interior.Color = RGB(255, colorIntensity, colorIntensity)
        Else
            ' Clear fill if not numeric
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell
End Sub

How the Script Works

  1. Calculates minimum and maximum numbers in the range.

  2. Scales each number to a color intensity (lighter for lower values).

  3. Applies a fill color dynamically to create a heatmap.

  4. Leaves non-numeric or empty cells blank.

Example Outcome

  • Cells with lower values appear light pink or near white.

  • Cells with higher values appear darker red.

  • The entire sheet becomes a smooth, visually meaningful heatmap.

Benefits of This Approach

  • No need for manual conditional formatting.

  • Dynamic response to different datasets.

  • Full control through a reusable VBA macro.

  • Extremely fast even for large data ranges.

Outcome

  1. We built a fast, dynamic, visually powerful heatmap simply by writing a clear prompt and letting AI translate it into effective VBA code.

  2. No complicated setup. No advanced Excel certifications required.

  3. Just logical prompting and smart automation.

The Bigger Lesson

  • "When you know how to describe your problem clearly, AI will do the heavy lifting — beautifully and effortlessly."

Call to Action

  • Facing complex reporting challenges? Want your Excel sheets to work smarter, not harder?

  • Contact Us. Let's automate the boring, and visualize the powerful.

bottom of page