Formula Cell Colorizer: See Your Entire Sheet's Logic in One Glance

TL;DR: You inherit a complex tax model from a departed senior. Is that Gross Profit number a formula or did someone type it? Is the column of adjustments calculating from the TB or were they keyed in by hand? This macro paints every cell by type — formulas in blue, hard-coded numbers in yellow, text in green — so you can see the entire sheet’s logic in one glance. Ctrl+Z restores everything.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workpaper where you want to understand what’s calculated vs. what’s entered
• The macro reads from the active sheet (or all sheets) and applies fill colors
• Ctrl+Z after running the macro undoes all the color changes in one action

What the macro does:

• Asks one question: “All visible sheets or active sheet only?”
• Applies three pastel fill colors based on cell type:
  • Light blue — formula cells (anything starting with =)
  • Light yellow — hard-coded numeric cells (typed numbers)
  • Light green — text cells (labels, descriptions, notes)
• Reports the count of each cell type and the number of sheets colorized
• Leaves blank cells and error cells untouched

Limitations:

• Formula cells are colored blue regardless of what the formula returns — a formula that produces a number, text, or error all count as formulas
• Cells with formula errors (#REF!, #N/A, etc.) are treated as formulas and colored blue. The error value is still visible through the fill
• Dates are treated as hard-coded numbers and colored yellow — they are stored as numeric values in Excel. If your sheet has many date columns, expect a lot of yellow
• The fill colors overwrite any existing background color. Run the macro on a copy if you need to preserve original formatting
• SpecialCells works on the UsedRange only — cells outside Excel’s tracked used range are not colorized (they’re blank anyway)

#The Macro

Option Explicit

Sub FormulaCellColorizer()
    ' ── Formula Cell Colorizer ─────────────────────────
    ' Applies a visual "heat map" to every cell on the
    ' active sheet (or all visible sheets): formulas =
    ' blue, hard-coded numbers = yellow, text = green.
    ' Blanks and errors are left untouched.
    '
    ' Non-destructive formatting change — Ctrl+Z restores
    ' all original fills in one undo step.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, scope As String
    Dim fCount As Long, nCount As Long, tCount As Long
    Dim wsCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Scope choice ───────────────────────────────────
    scope = InputBox("Color-code by cell type:" & vbCrLf & _
        "  Y = All visible sheets" & vbCrLf & _
        "  N = Active sheet only", _
        "Formula Cell Colorizer", "Y")
    If scope = "" Then GoTo CleanUp
    scope = UCase(Left(scope, 1))

    fCount = 0: nCount = 0: tCount = 0: wsCount = 0

    If scope = "Y" Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then
                ColorizeOneSheet ws, fCount, nCount, tCount
                wsCount = wsCount + 1
            End If
        Next ws
    Else
        ColorizeOneSheet ActiveSheet, fCount, nCount, tCount
        wsCount = 1
    End If

    ' ── Summary ────────────────────────────────────────
    Dim msg As String
    msg = "Colorized " & wsCount & " sheet(s):" & vbCrLf & vbCrLf & _
          "  Blue   = " & fCount & " formula cell(s)" & vbCrLf & _
          "  Yellow = " & nCount & " hard-coded number(s)" & vbCrLf & _
          "  Green  = " & tCount & " text cell(s)" & vbCrLf & vbCrLf & _
          "Press Ctrl+Z to undo all color changes."
    MsgBox msg, vbInformation, "Colorization Complete"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Colorize a single sheet ────────────────────
Private Sub ColorizeOneSheet(ByRef ws As Worksheet, _
                              ByRef fCount As Long, _
                              ByRef nCount As Long, _
                              ByRef tCount As Long)
    Dim usedRng As Range, rng As Range

    ' ── Determine used range ───────────────────────────
    On Error Resume Next
    Set usedRng = ws.UsedRange
    On Error GoTo 0
    If usedRng Is Nothing Then Exit Sub

    ' ── Formula cells → light blue ─────────────────────
    On Error Resume Next
    Set rng = usedRng.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not rng Is Nothing Then
        fCount = fCount + rng.Cells.Count
        rng.Interior.Color = RGB(219, 234, 254)
    End If

    ' ── Hard-coded numbers → light yellow ──────────────
    On Error Resume Next
    Set rng = usedRng.SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0
    If Not rng Is Nothing Then
        nCount = nCount + rng.Cells.Count
        rng.Interior.Color = RGB(254, 243, 199)
    End If

    ' ── Text cells → light green ───────────────────────
    On Error Resume Next
    Set rng = usedRng.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    If Not rng Is Nothing Then
        tCount = tCount + rng.Cells.Count
        rng.Interior.Color = RGB(220, 252, 231)
    End If
End Sub

#How It Works

#Why SpecialCells instead of looping

The macro uses SpecialCells to find cells by type in one call, rather than looping through every cell on the sheet:

Set rng = usedRng.SpecialCells(xlCellTypeFormulas)
If Not rng Is Nothing Then
    rng.Interior.Color = RGB(219, 234, 254)
End If

SpecialCells returns a Range object containing every cell matching the criteria — formulas, constants of a specific type, blanks, etc. Applying a fill color to the returned range is a single operation, not one per cell. On a sheet with 2,000 formula cells, this is 2,000× faster than For Each cell.

The trade-off: SpecialCells raises a runtime error if no cells match the criteria. Each call is wrapped in On Error Resume Next / On Error GoTo 0 so a sheet with no formulas (all constant data) doesn’t crash the macro.

#Formula cells are always blue — regardless of what the formula returns

A formula that returns a number (=SUM(A1:A10) → $45,000), text (=IF(A1>0,"Over","Under")), or an error (=VLOOKUP(...) → #N/A) all get colored blue. The distinction this macro makes is not “what does the cell display?” but “how did it get there?” — calculated or entered.

This means a cell showing $45,000 in blue was computed. The same number in yellow was typed. The visual cue is immediate even though both cells display the same thing.

#Three categories, not two — why text gets its own color

Most “formula vs. constant” macros only distinguish two categories: formula or not. This macro uses three because tax workpapers have a third category that dominates: text. Account descriptions, client names, preparer initials, period labels, section headers — these are constants, but grouping them with hard-coded dollar amounts (“yellow”) buries the signal in noise.

A column of 40 account descriptions and 3 hard-coded numbers in a sea of yellow is invisible. The same column with 40 green cells and 3 yellow cells is instantly legible. The yellow cells pop because they’re the exception.

#The used range keeps it fast

Set usedRng = ws.UsedRange

Instead of operating on ws.Cells (all 17 billion cells on a worksheet), the macro limits itself to Excel’s tracked used range — the smallest rectangle containing all cells with data, formatting, or formulas. On a typical tax workpaper with 500 rows and 10 columns, this checks 5,000 cells instead of billions.

The On Error Resume Next wrapper is necessary because UsedRange can be Nothing on a completely empty sheet (no data, no formatting, never touched). Sheets like that are skipped.

#Ctrl+Z restores everything

All three SpecialCells calls plus the Interior.Color assignments across all sheets are recorded as a single VBA operation in Excel’s undo stack. Pressing Ctrl+Z after running the macro removes every fill color in one undo. Unlike macros that modify cell values or delete sheets (which fragment the undo stack), a pure formatting macro like this one is fully reversible.

This design choice is intentional. The preparer runs the macro on a 14-sheet workbook, reviews the color pattern, finds the three yellow outliers in the formula column, investigates — then hits Ctrl+Z and the workbook is clean again. No “save a copy first” required (though it’s never a bad idea).

#Why only visible sheets

If ws.Visible = xlSheetVisible Then

Hidden sheets are skipped. If you’ve hidden calculation backup tabs or intermediate workpapers, they stay as they are. The macro only colorizes sheets the preparer is actively working with.

To include hidden sheets, remove the visibility check — but be aware that hidden sheets are usually hidden for a reason, and colorizing them adds visual clutter you won’t see until you unhide them.

#One InputBox for scope — no code editing

The macro asks a single yes/no question at startup:

scope = InputBox("Color-code by cell type:" & vbCrLf & _
    "  Y = All visible sheets" & vbCrLf & _
    "  N = Active sheet only", _
    "Formula Cell Colorizer", "Y")

The default is “Y” (all sheets) because the most common use case is a first-time audit of an inherited workbook — you want the full picture. Pressing Enter with no change colorizes everything.

If the preparer only wants to check one sheet, they type “N” and the macro only touches the active sheet. No constant to edit in the VBA editor, no parameter cells on a config sheet. One question at runtime.

#Adapt It