Bulk Tab Colorer: Color-Code Every Sheet Tab by Category in One Click

TL;DR: This macro reads a lookup table on a “Tab-Colors” sheet — Keyword and Color columns — and assigns tab colors to every sheet in the workbook. A sheet named “Sched-A — Income” matches the keyword “Sched” and gets blue. “State Calc — CA” matches “State” and gets purple. No match? Default gray. If the lookup table doesn’t exist yet, the macro creates one pre-populated with standard tax workpaper categories.

The Problem

You inherit a 28-tab workpaper file for the Chen-Volpe consolidated return. The preparer named the tabs like a cryptographer: “S-1A”, “S-1B”, “TB-CONSOL”, “FA-DET”, “STATE-CA”, “STATE-NY”, “SUM-FED”, “JES-Q4”, and nineteen more. You spend the first 10 minutes of every review session clicking tabs one at a time looking for the fixed asset schedule. Your eyes read “S-1A” as “Sched-1A” three tabs before you actually find it.

Tab colors solve this. But coloring 28 tabs by hand — right-click, Tab Color, pick from the palette, repeat — takes five minutes and looks slightly different every time. This macro does all 28 in under a second, consistently, using rules you define once on a lookup sheet.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with multiple sheets
No sheet protection on the workbook structure (tab colors are a workbook-level property — if “Protect Workbook” is on, the macro will skip protected sheets)

Limitations:

Color matching is keyword-based and first-match-wins — a sheet named “Schedule-Depreciation” matches “Sched” (first in the lookup table) before it matches “Depr.” Reorder your lookup table to control priority
Keyword matching is case-insensitive and substring-based — “Sched” matches “Schedule A”, “sched-B”, and “DEPR-SCHED-2025”
If a sheet name doesn’t match any keyword, it gets a light gray default color
The lookup table supports named colors (see the list below). If you need a custom color not on the list, edit the ColorNameToRGB function in the VBA

Supported color names: Green, Blue, Red, Orange, Purple, Yellow, Gray, Dark Blue, Teal, Pink, Brown, Lime, Indigo, Black, White, Lavender, Mint, Coral, Slate, Gold, and None (no tab color). If you need a different color, add it to the ColorNameToRGB function.

#The Macro

Option Explicit

Sub BulkTabColorer()
    ' ── Bulk Tab Colorer ────────────────────────────────
    ' Colors sheet tabs by category using a lookup table
    ' on the "Tab-Colors" sheet. Edit the table — not
    ' the VBA — to customize categories and colors.
    '
    ' If Tab-Colors doesn't exist, the macro creates it
    ' pre-populated with standard tax workpaper categories.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim luSheet As Worksheet
    Dim ws As Worksheet
    Dim luRow As Long, luLast As Long
    Dim keyword As String, colorName As String
    Dim rgbVal As Long
    Dim matchCount As Long, noMatchCount As Long
    Dim catCount As Long
    Dim found As Boolean

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

    ' ── Step 1: Find or build the lookup table ─────────
    On Error Resume Next
    Set luSheet = ThisWorkbook.Worksheets("Tab-Colors")
    On Error GoTo CleanUp

    If luSheet Is Nothing Then
        Set luSheet = BuildDefaultLookup()
        MsgBox "Tab-Colors lookup sheet created with 12 default " & _
               "categories." & vbCrLf & vbCrLf & _
               "Edit the Keyword and Color columns, " & _
               "then run this macro again.", vbInformation, _
               "Lookup Table Created"
        GoTo CleanUp
    End If

    ' ── Step 2: Read the lookup table ──────────────────
    luLast = luSheet.Cells(luSheet.Rows.Count, 1).End(xlUp).Row
    If luLast < 2 Then
        MsgBox "Tab-Colors sheet has no data. " & vbCrLf & _
               "Add Keyword / Color rows and try again.", _
               vbExclamation, "Empty Lookup"
        GoTo CleanUp
    End If

    ' ── Step 3: Color every sheet tab ──────────────────
    matchCount = 0
    noMatchCount = 0
    catCount = luLast - 1

    For Each ws In ThisWorkbook.Worksheets
        found = False

        For luRow = 2 To luLast
            keyword = Trim(CStr(luSheet.Cells(luRow, 1).Value))
            colorName = Trim(CStr(luSheet.Cells(luRow, 2).Value))
            If keyword = "" Or colorName = "" Then GoTo NextKeyword

            If InStr(1, ws.Name, keyword, vbTextCompare) > 0 Then
                rgbVal = ColorNameToRGB(colorName)
                On Error Resume Next
                ws.Tab.Color = rgbVal
                On Error GoTo CleanUp
                matchCount = matchCount + 1
                found = True
                Exit For
            End If
NextKeyword:
        Next luRow

        If Not found Then
            On Error Resume Next
            ws.Tab.Color = RGB(180, 180, 180)  ' default gray
            On Error GoTo CleanUp
            noMatchCount = noMatchCount + 1
        End If
    Next ws

    ' ── Step 4: Report ─────────────────────────────────
    MsgBox "Colored " & matchCount & " sheet(s) using " & _
           catCount & " categories." & vbCrLf & _
           noMatchCount & " sheet(s) received default gray " & _
           "(no keyword match)." & vbCrLf & vbCrLf & _
           "To customize, edit the Tab-Colors sheet.", _
           vbInformation, "Done"

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

' ── Build the default Tab-Colors lookup sheet ──────────
Private Function BuildDefaultLookup() As Worksheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Sheets(1))
    ws.Name = "Tab-Colors"

    ' Headers
    ws.Cells(1, 1) = "Keyword"
    ws.Cells(1, 2) = "Color"
    ws.Cells(1, 3) = "Description"
    ws.Range("A1:C1").Font.Bold = True
    ws.Range("A1:C1").Interior.Color = RGB(50, 50, 50)
    ws.Range("A1:C1").Font.Color = vbWhite

    ' Default tax workpaper categories
    ws.Cells(2, 1)  = "TB":       ws.Cells(2, 2)  = "Green":    ws.Cells(2, 3)  = "Trial Balance"
    ws.Cells(3, 1)  = "Sched":    ws.Cells(3, 2)  = "Blue":     ws.Cells(3, 3)  = "Schedule (A-F, etc.)"
    ws.Cells(4, 1)  = "Summ":     ws.Cells(4, 2)  = "Red":      ws.Cells(4, 3)  = "Summary / Cover"
    ws.Cells(5, 1)  = "Fixed":    ws.Cells(5, 2)  = "Orange":   ws.Cells(5, 3)  = "Fixed Assets"
    ws.Cells(6, 1)  = "Depr":     ws.Cells(6, 2)  = "Orange":   ws.Cells(6, 3)  = "Depreciation"
    ws.Cells(7, 1)  = "State":    ws.Cells(7, 2)  = "Purple":   ws.Cells(7, 3)  = "State Tax"
    ws.Cells(8, 1)  = "Apport":   ws.Cells(8, 2)  = "Purple":   ws.Cells(8, 3)  = "Apportionment"
    ws.Cells(9, 1)  = "NOL":      ws.Cells(9, 2)  = "Yellow":   ws.Cells(9, 3)  = "NOL / Carryforwards"
    ws.Cells(10, 1) = "Credit":   ws.Cells(10, 2) = "Yellow":   ws.Cells(10, 3) = "Tax Credits"
    ws.Cells(11, 1) = "JE":       ws.Cells(11, 2) = "Gray":     ws.Cells(11, 3) = "Journal Entries"
    ws.Cells(12, 1) = "Adj":      ws.Cells(12, 2) = "Gray":     ws.Cells(12, 3) = "Adjustments"
    ws.Cells(13, 1) = "Note":     ws.Cells(13, 2) = "Dark Blue": ws.Cells(13, 3) = "Notes / Instructions"

    ws.Columns("A:C").AutoFit
    Set BuildDefaultLookup = ws
End Function

' ── Convert a color name to an RGB value ───────────────
Private Function ColorNameToRGB(name As String) As Long
    Select Case LCase(name)
        Case "green":       ColorNameToRGB = RGB(0, 176, 80)
        Case "blue":        ColorNameToRGB = RGB(0, 112, 192)
        Case "red":         ColorNameToRGB = RGB(255, 0, 0)
        Case "orange":      ColorNameToRGB = RGB(237, 125, 49)
        Case "purple":      ColorNameToRGB = RGB(112, 48, 160)
        Case "yellow":      ColorNameToRGB = RGB(255, 192, 0)
        Case "gray":        ColorNameToRGB = RGB(165, 165, 165)
        Case "dark blue":   ColorNameToRGB = RGB(0, 32, 96)
        Case "teal":        ColorNameToRGB = RGB(0, 150, 136)
        Case "pink":        ColorNameToRGB = RGB(233, 30, 99)
        Case "brown":       ColorNameToRGB = RGB(121, 85, 72)
        Case "lime":        ColorNameToRGB = RGB(139, 195, 74)
        Case "indigo":      ColorNameToRGB = RGB(63, 81, 181)
        Case "black":       ColorNameToRGB = RGB(50, 50, 50)
        Case "white":       ColorNameToRGB = RGB(255, 255, 255)
        Case "lavender":    ColorNameToRGB = RGB(179, 157, 219)
        Case "mint":        ColorNameToRGB = RGB(0, 200, 150)
        Case "coral":       ColorNameToRGB = RGB(255, 128, 128)
        Case "slate":       ColorNameToRGB = RGB(120, 144, 156)
        Case "gold":        ColorNameToRGB = RGB(255, 183, 77)
        Case "none":        ColorNameToRGB = -1
        Case Else:          ColorNameToRGB = RGB(180, 180, 180)
    End Select
End Function

#How It Works

#The Tab-Colors sheet is the configuration — not the VBA

This is the core design decision. Instead of hard-coding categories in the VBA (or — worse — making the user edit magic string constants), the lookup table is a regular Excel sheet that anyone can edit. Want a new category for “Cover” sheets? Type Cover in column A and Teal in column B. Done.

The macro reads column A (Keyword) and column B (Color) starting at row 2. For each sheet in the workbook, it runs down the lookup rows. First keyword that appears anywhere inside the sheet name (case-insensitive) gets the associated color. If no keyword matches, the sheet gets light gray — a visual “uncategorized” signal that catches your eye.

For luRow = 2 To luLast
    keyword = Trim(CStr(luSheet.Cells(luRow, 1).Value))
    colorName = Trim(CStr(luSheet.Cells(luRow, 2).Value))
    If keyword = "" Or colorName = "" Then GoTo NextKeyword

    If InStr(1, ws.Name, keyword, vbTextCompare) > 0 Then
        rgbVal = ColorNameToRGB(colorName)
        ws.Tab.Color = rgbVal
        matchCount = matchCount + 1
        found = True
        Exit For  ' first match wins — stop checking
    End If
NextKeyword:
Next luRow

The Exit For after the match is critical — it means the order of rows in the lookup table controls priority. If your lookup has "Sched" (Blue) on row 3 and "Depr" (Orange) on row 6, a sheet named “Sched Depreciation” gets Blue because the loop encounters “Sched” first.

#If the lookup sheet doesn’t exist, the macro creates it

First run on a fresh workbook? The BuildDefaultLookup function creates the Tab-Colors sheet pre-loaded with 12 standard tax workpaper categories: trial balances, schedules, summaries, fixed assets, depreciation, state tax, apportionment, NOLs, credits, journal entries, adjustments, and notes. Each has a sensible color and a description column so the lookup is self-documenting.

On Error Resume Next
Set luSheet = ThisWorkbook.Worksheets("Tab-Colors")
On Error GoTo CleanUp

If luSheet Is Nothing Then
    Set luSheet = BuildDefaultLookup()
    MsgBox "Tab-Colors lookup sheet created with 12 default categories." & _
           vbCrLf & vbCrLf & _
           "Edit the Keyword and Color columns, then run this macro again.", _
           vbInformation, "Lookup Table Created"
    GoTo CleanUp
End If

The macro exits after creating the defaults — it doesn’t auto-run the coloring on the first pass. This gives you a chance to review the defaults, add firm- specific categories, and then run it a second time to apply them. Running it twice on a new workbook is a minor friction, but the benefit is that you’re never surprised by which colors get assigned.

#Color names, not RGB codes

Nobody wants to type RGB(0, 112, 192) into a spreadsheet cell. The ColorNameToRGB function maps 20 human-readable names to their RGB equivalents. The naming is deliberately loose — "dark blue" works, "Dark Blue" works, "dArK bLuE" works. The function uses LCase() for case-insensitive comparison.

Case "green":       ColorNameToRGB = RGB(0, 176, 80)
Case "blue":        ColorNameToRGB = RGB(0, 112, 192)
Case "red":         ColorNameToRGB = RGB(255, 0, 0)

If you type a color name that isn’t recognized — "Chartreuse", for example — the Case Else falls back to gray. The macro doesn’t error out; you just get a gray tab and can fix the spelling on the lookup sheet.

To add a new color, open the VBA editor and add a new Case line. Or use an existing named color that’s close enough — “Lime” for bright green, “Teal” for blue-green, “Slate” for blue-gray.

#Why a default gray for unmatched sheets

Sheets that match no keyword get RGB(180, 180, 180) — a light gray. This is intentional. A sheet with no color (the Excel default) looks identical to a sheet you haven’t processed yet. A light gray tab says “I was processed but nothing matched.” You see it immediately and can either add a keyword to the lookup table or rename the sheet.

If you prefer uncolored tabs for unmatched sheets, set the ColorNameToRGB fallback to return -1 (which is xlNone in VBA):

Case Else: ColorNameToRGB = -1  ' no tab color

#Error handling: protected sheets get skipped, not crashed

Some workbooks have protected sheet tabs. Setting .Tab.Color on a protected sheet raises an error. The macro wraps the color assignment in On Error Resume Next so a single protected sheet doesn’t kill the whole operation:

On Error Resume Next
ws.Tab.Color = rgbVal
On Error GoTo CleanUp

The protected sheet simply stays its current color. The message box reports the count of sheets colored vs. unmatched, not protected vs. unprotected — keep it simple.

#Why ScreenUpdating gets toggled

The macro loops through every sheet and writes a property on each one. Without ScreenUpdating = False, the Excel window repaints after every tab color write. For a 30-sheet workbook, that’s 30 redraws — each one taking a fraction of a second that adds up to noticeable lag. Toggling it off makes the macro complete in under a second regardless of workbook size.

#Adapt It

Add your firm’s categories. Edit the Tab-Colors sheet directly — add keywords in column A and color names in column B. Common additions for CPA firms:

Keyword   | Color      | Description
K-1       | Pink       | Schedule K-1 detail
PY        | Slate      | Prior-year reference
CY        | Mint       | Current-year workpapers
Tax-Calc  | Gold       | Tax calculation worksheets
Wrkpr     | Lavender   | Generic workpaper

Override the default lookup categories. If you don’t like the built-in 12 categories, delete them from the Tab-Colors sheet and add your own. The macro only reads whatever is on the lookup sheet — it has no hard-coded categories.

' Delete all rows 2–13 on Tab-Colors, then add:
' Keyword   | Color    | Description
' FS        | Blue     | Financial statements
' WK        | Green    | Workpapers
' PF        | Orange   | Partner files
' ARC       | Gray     | Archive

Change the default gray. Unmatched sheets get RGB(180, 180, 180). To make unmatched sheets stand out more (pale yellow?) or less (no color), change the RGB value in the body of the macro:

' No match — default fallback
ws.Tab.Color = RGB(255, 255, 200)  ' pale yellow

Make unmatched sheets stay uncolored. Replace the RGB line with -1:

ws.Tab.Color = -1  ' Excel's "no color" constant

Add a custom color to the function. If you need a color not in the 20 supported names — say a specific shade of amber for tax provision sheets — add a Case line to ColorNameToRGB:

Case "amber":   ColorNameToRGB = RGB(255, 191, 0)

Run on the active workbook from your Personal Macro Workbook. Store this macro in PERSONAL.XLSB so it’s available in any workbook. The ThisWorkbook reference will point to your Personal Macro Workbook — change it to ActiveWorkbook:

' Change every instance of ThisWorkbook to ActiveWorkbook:
Set luSheet = ActiveWorkbook.Worksheets("Tab-Colors")
Set ws = ActiveWorkbook.Worksheets.Add(...)

Skip the Tab-Colors sheet itself. The macro processes every sheet including the lookup sheet. Add a skip at the top of the loop:

If ws.Name = "Tab-Colors" Then GoTo NextSheet
' ... keyword matching and coloring ...
NextSheet:

See also: Sheet Tab Normalizer — rename all your tabs to a consistent format before color-coding them. Bulk Sheet Sorter — after coloring, sort your tabs to group categories together.

Download the bulk tab colorer sample workbook (18 sheets: TB, schedules A–F, fixed assets, depreciation, state calcs, summaries, JE log, notes, and Instructions)

xlsx 11 KB