Row & Column Highlighter: Trace Values Across Wide Workpapers Without Losing Your Place

TL;DR: Toggle this macro on, and every cell you click lights up its entire row and column with a pale yellow highlight. Click another cell, the highlight follows you. Toggle it off and all original colors come back. No Excel 2024 required — works on any desktop version from 2013 onward.

#Prerequisites & Setup

What you’ll need:

• Excel 2013+ (desktop)
• A workbook with wide schedules — trial balances, fixed asset registers, or any workpaper where you need to trace values across many columns
• The macro goes in a different module than usual — see the note below

Where to paste the code: This is the first macro on the blog that goes in the ThisWorkbook module instead of a regular module. Here’s why: it uses a Workbook_SheetSelectionChange event handler, which only fires when placed in ThisWorkbook. Paste it anywhere else and nothing happens.

To paste correctly:

1. Press Alt+F11 to open the VBA editor
2. In the Project Explorer (left panel), find your workbook name
3. Double-click the ThisWorkbook item (not a Module, not a Sheet)
4. Paste the entire macro into the blank window that opens
5. Press Ctrl+S to save

Limitations:

• Only one instance can be active — toggling it on again while it’s already running has no effect
• The highlight uses a light fill color. Cells that already have a fill color get their original color restored when the macro is turned off
• Protected sheets will raise an error when the macro tries to highlight them — the macro skips protected sheets automatically
• The toggle state is stored in a module-level variable and resets if you close and reopen the workbook. You’ll need to toggle it on each time

#The Macro

Option Explicit

' ── Row & Column Highlighter ─────────────────────────
' Paste this ENTIRE code block into the ThisWorkbook
' module (double-click "ThisWorkbook" in the VBA
' Project Explorer). The Workbook_SheetSelectionChange
' event must live in ThisWorkbook to fire.
'
' HOW TO USE:
'   1. Alt+F8 → ToggleRowColHighlight → Run (turns ON)
'   2. Click any cell — its entire row and column light up
'   3. Click another cell — highlights follow
'   4. Alt+F8 → ToggleRowColHighlight → Run (turns OFF)
'      Original cell colors are restored.
'
' Paste into: THISWORKBOOK module (not a standard module)
' ────────────────────────────────────────────────────────

' ── Module-level state ────────────────────────────────
Private highlightOn As Boolean
Private savedEntries As Collection  ' each entry: "Sheet!A1=12345"

' ── Toggle: turn highlighting ON or OFF ──────────────
Public Sub ToggleRowColHighlight()
    If Not highlightOn Then
        ' ── TURN ON ───────────────────────────────────
        highlightOn = True
        Set savedEntries = New Collection

        ' Highlight the current selection immediately
        IlluminateRowCol ActiveCell

        MsgBox "Row/column highlighting ON." & vbCrLf & vbCrLf & _
               "Click any cell to highlight its row and column." & vbCrLf & _
               "Run ToggleRowColHighlight again to turn OFF.", _
               vbInformation, "Highlighting ON"
    Else
        ' ── TURN OFF ──────────────────────────────────
        RestoreAllColors
        highlightOn = False
        Set savedEntries = Nothing

        MsgBox "Row/column highlighting OFF." & vbCrLf & vbCrLf & _
               "All original cell colors have been restored.", _
               vbInformation, "Highlighting OFF"
    End If
End Sub

' ── Event: fires every time the user selects a cell ──
Private Sub Workbook_SheetSelectionChange( _
    ByVal Sh As Object, _
    ByVal Target As Range)

    If Not highlightOn Then Exit Sub
    If Target Is Nothing Then Exit Sub

    On Error GoTo ErrHandler

    ' Restore previous highlight, then apply new one
    RestoreAllColors
    IlluminateRowCol Target.Cells(1)
    Exit Sub

ErrHandler:
    ' Protected sheets, merged cells, etc. — skip silently
End Sub

' ── Core: light up the row and column of a cell ──────
Private Sub IlluminateRowCol(ByRef cell As Range)
    Dim ws As Worksheet
    Dim rngRow As Range, rngCol As Range
    Dim cl As Range
    Dim addr As String

    Set ws = cell.Worksheet

    ' Skip protected sheets
    If ws.ProtectContents Then Exit Sub

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' ── Save current colors BEFORE applying highlight ─
    Set rngRow = ws.Rows(cell.Row)
    For Each cl In Intersect(rngRow, ws.UsedRange)
        If Not IsEmpty(cl) Then
            SaveCellColor ws, cl
        End If
    Next cl

    Set rngCol = ws.Columns(cell.Column)
    For Each cl In Intersect(rngCol, ws.UsedRange)
        If Not IsEmpty(cl) Then
            addr = ws.Name & "!" & cl.Address(False, False)
            ' Skip intersection cell — already saved from row pass
            If addr <> ws.Name & "!" & cell.Address(False, False) Then
                SaveCellColor ws, cl
            End If
        End If
    Next cl

    ' ── Apply highlight ───────────────────────────────
    With Intersect(rngRow, ws.UsedRange)
        .Interior.Color = RGB(255, 255, 200)   ' pale yellow
    End With
    With Intersect(rngCol, ws.UsedRange)
        .Interior.Color = RGB(255, 255, 200)
    End With

    ' Intersection cell: slightly darker so it stands out
    With Intersect(rngRow, rngCol)
        .Interior.Color = RGB(255, 255, 140)   ' slightly darker yellow
    End With

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

' ── Helper: save a cell's original color ─────────────
Private Sub SaveCellColor(ws As Worksheet, cl As Range)
    Dim key As String
    key = ws.Name & "!" & cl.Address(False, False) & "=" & cl.Interior.Color

    ' Only save if not already in the collection
    On Error Resume Next
    savedEntries.Add key, key
    On Error GoTo 0
End Sub

' ── Helper: restore all saved cell colors ─────────────
Private Sub RestoreAllColors()
    If savedEntries Is Nothing Then Exit Sub
    If savedEntries.Count = 0 Then Exit Sub

    Dim entry As Variant
    Dim parts() As String
    Dim addrParts() As String
    Dim ws As Worksheet
    Dim clr As Long
    Dim i As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    For i = savedEntries.Count To 1 Step -1
        entry = savedEntries(i)
        ' Entry format: "SheetName!A1=12345"
        parts = Split(entry, "=")
        If UBound(parts) >= 1 Then
            clr = CLng(parts(1))
            addrParts = Split(parts(0), "!")
            If UBound(addrParts) >= 1 Then
                On Error Resume Next
                Set ws = ThisWorkbook.Worksheets(addrParts(0))
                If Not ws Is Nothing Then
                    If Not ws.ProtectContents Then
                        ws.Range(addrParts(1)).Interior.Color = clr
                    End If
                End If
                On Error GoTo 0
            End If
        End If
        savedEntries.Remove i
    Next i

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

#How It Works

#Two halves, one module: the toggle and the event

This macro has two parts that work together. The ToggleRowColHighlight sub is a regular macro you run from Alt+F8 — it turns the highlighting on and off. The Workbook_SheetSelectionChange event fires automatically every time you click a cell — but only when highlightOn is True.

The event handler lives in ThisWorkbook because that’s where Excel looks for workbook-level events. A standard module won’t fire Workbook_SheetSelectionChange — the event must be attached to the ThisWorkbook object.

Private Sub Workbook_SheetSelectionChange( _
    ByVal Sh As Object, _
    ByVal Target As Range)

    If Not highlightOn Then Exit Sub    ' do nothing when toggled off
    ...

The first line of the event handler is a gate: if highlighting isn’t active, do nothing and return immediately. This keeps the macro from interfering with normal Excel use when it’s turned off.

#Saving and restoring original colors — the undo system

This is the trickiest part of the macro and the reason it costs ~60 lines instead of 15. Without color restoration, toggling the highlight off would leave every cell in the workbook with a permanent yellow fill. That’s worse than the original problem.

The savedEntries Collection stores each cell’s identity and original color as a delimited string: "SheetName!A23=12345". The portion before the = is the cell’s full address (sheet name + range), and the portion after is the original Interior.Color value as a number.

key = ws.Name & "!" & cl.Address(False, False) & "=" & cl.Interior.Color
savedEntries.Add key, key

Why use a single delimited string instead of a Dictionary with separate keys and values? Because VBA’s built-in Collection doesn’t expose keys during iteration — you can look up by key, but you can’t enumerate the keys. By packing both address and color into one string, we can iterate by index and Split on the = to recover both pieces.

The SaveCellColor helper uses On Error Resume Next to skip duplicate entries (Collection.Add raises an error if the key already exists). This prevents overwriting a saved original color with a highlight color if the user clicks the same cell twice.

Restoring works in reverse: iterate the collection from last to first (removing entries as we go), Split each entry on =, Split the address half on ! to get the sheet name and range, then reassign the original color. Cells that had no fill (-4142 or xlNone) go back to having no fill. Cells that had a custom color get exactly that color back. Protected sheets are skipped during restore so a single locked tab doesn’t break the cleanup.

#Why pale yellow

The highlight color is RGB(255, 255, 200) — a pale, warm yellow. Dark enough to be visible against a white background, light enough to read numbers through, and warm enough that it doesn’t look like the standard Excel selection color (which is gray-blue). The active cell gets a slightly darker shade (RGB(255, 255, 140)) so your eyes can find it instantly within the highlighted crosshair.

These RGB values are chosen to work on both LCD monitors (where yellow can wash out) and printed pages (where the highlight doesn’t print unless you have background printing enabled). If you prefer a different color, change the RGB values in IlluminateRowCol.

#Intersect with UsedRange — don’t highlight 1,048,576 empty rows

With Intersect(rngRow, ws.UsedRange)
    .Interior.Color = RGB(255, 255, 200)
End With

Without Intersect, ws.Rows(cell.Row) would try to highlight every cell from column A to column XFD — all 16,384 of them. Many of those cells are empty and will never be seen. Intersect with UsedRange limits the highlight to only the region of the sheet that actually contains data. On a sheet where data ends at column P and row 250, the highlight only spans that block.

The cost: UsedRange can drift over time (leftover formatting in row 50,000 inflates it). If the highlight seems to extend far beyond your data, run the last-cell-resetter macro first.

#Why we disable events during highlighting

Application.EnableEvents = False
...
Application.EnableEvents = True

When the macro writes .Interior.Color to hundreds of cells, each write would normally fire Workbook_SheetSelectionChange again — creating an infinite recursive loop that freezes Excel. Disabling events during the highlight pass tells Excel “don’t fire any event handlers while I’m doing this.” Events are re-enabled before the sub exits so normal Excel behavior resumes.

If the macro crashes mid-highlight (protected sheet, out-of-memory, etc.), the On Error GoTo ErrHandler pattern in the event handler catches the error and returns — but EnableEvents stays False because we’re inside the event, not the toggle. This is handled by the error handler at the event level. For the initial toggle and the RestoreAllColors restore pass, we re-enable events in the Finally pattern. Between both guards, the user should never be left with events disabled.

#Protected sheets are skipped, not crashed

If ws.ProtectContents Then Exit Sub

Writing .Interior.Color to a protected sheet raises a run-time error. Rather than showing an ugly error dialog every time the user clicks on a protected tab, the macro checks ProtectContents and silently skips protected sheets. The user sees no highlight on that sheet, which is a visual cue that it’s protected — and they can unprotect it with the sheet-lock-unlock macro if they want highlighting there.

#Why the toggle state doesn’t survive a close/reopen

highlightOn and savedEntries are module-level Private variables. They live in memory while the workbook is open and get wiped when the workbook closes. This is intentional — you wouldn’t want the macro to auto-enable every time you open the file. Toggle-on is always a conscious decision.

#Adapt It