🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA ColorIndex in Excel — the 56-Color Palette and Why Your Color Comes Out Wrong

|

VBA ColorIndex in Excel — the 56-Color Palette and Why Your Color Comes Out Wrong

TL;DR — ColorIndex is not a color. It is a seat number — an index from 1 to 56 into a palette the workbook owns. .Color names an absolute color; .ColorIndex names a slot, and who sits in that slot can change from one workbook to the next. That is why the same index looks gold on your machine and mustard on a colleague's. For new code, set an absolute color with .Color = RGB(...); keep ColorIndex for the two things .Color cannot express — xlNone (no fill) and xlColorIndexAutomatic — and for matching an old palette-based workbook.

' Two different number spaces. Do not mix them.
Range("A1").Interior.ColorIndex = 6           ' slot 6 of the palette -> yellow (usually)
Range("A2").Interior.Color = RGB(255, 255, 0) ' an absolute color -> yellow, always

' The two specials only ColorIndex can express:
Range("A3").Interior.ColorIndex = xlNone                 ' remove the fill entirely
Range("A4").Font.ColorIndex = xlColorIndexAutomatic      ' "let Excel decide" (black text)

Every color-bearing object in Excel — a cell fill (Interior), the Font, Borders, a chart series — exposes both a Color property and a ColorIndex property. They point at the same pixel but through two completely different number systems, and confusing them is the single most common "why is my color wrong" bug in VBA. This guide is about the older of the two — the palette index — because understanding what it really is explains a whole class of colors-look-different problems.

What you'll learn

  • The mental model — ColorIndex is a seat number in a 56-seat theater, not a color
  • The one rule that prevents most wrong colors — .Color and .ColorIndex are different number spaces
  • The palette trap — why an index can render differently in another workbook
  • The two special values — xlNone and xlColorIndexAutomatic — that .Color cannot express
  • Reading a color back, and why .ColorIndex on a .Color-set cell is lossy
  • When ColorIndex is still the right tool, and when to prefer .Color

The mental model: a seat number, not a color

Picture a theater with 56 numbered seats. ColorIndex = 6 does not mean "yellow." It means "whoever is sitting in seat 6." In a default workbook seat 6 happens to hold yellow, so it looks like yellow is built in. But the seating chart — the workbook's color palette — belongs to the workbook, and it can be reassigned. Move a different color into seat 6 and every cell you set to ColorIndex = 6 changes at once, without your code touching a thing.

.Color, by contrast, is not a seat number at all. Interior.Color = RGB(255, 255, 0) writes the actual color into the cell. There is no lookup, no chart, nothing to remap. That difference — slot versus absolute value — is the whole story, and it is the reason the two properties behave so differently when a workbook moves between machines or themes.

The rule that matters most: two number spaces, never mixed

Here is the mistake that produces most wrong colors. .Color and .ColorIndex accept numbers from totally different ranges, and a number that is valid for one is also valid — and means something else — for the other:

Range("A1").Interior.Color = 6        ' Color space: 6 = RGB(6,0,0) -> almost black
Range("A1").Interior.ColorIndex = 6   ' Index space: slot 6 -> yellow

Nobody means "almost black" when they type 6, but .Color = 6 gives exactly that, silently. The fix is a discipline, not a trick: decide which space you are in and stay there. If the line reads .Color, the right-hand side is an RGB(...) value or a vb* constant. If it reads .ColorIndex, the right-hand side is an integer 1 to 56 (or a special value below). Reading a .Color value into a ColorIndex property, or vice versa, is always a bug even when it compiles and runs.

The palette trap: why the index renders differently elsewhere

This is the real cost of the seat-number model. Because ColorIndex is a lookup into ActiveWorkbook.Colors(n), the color it produces is only as stable as that palette. Two things move the seats:

  • A workbook built from a template with a customized palette already has different colors in some slots. Your ColorIndex = 10 might be forest green in your file and a muddy olive in theirs.
  • Code can rewrite the palette directly (ActiveWorkbook.Colors(10) = RGB(...)), and modern theme colors add another remapping layer on top.

So a macro that fills report headers with ColorIndex = 37 looks perfect on the machine it was written on and subtly off on the client's — same code, same index, different seating chart. .Color = RGB(...) does not have this failure mode, because it never asks the palette anything. When a color must look identical everywhere, that is the deciding argument: absolute color travels, a palette index does not.

The two specials: xlNone and xlColorIndexAutomatic

There is one job only ColorIndex can do, and it is the honest reason to keep it around. .Color can express any of 16 million colors — but it cannot express "no color." Two negative constants fill that gap:

Range("A1").Interior.ColorIndex = xlNone                ' -4142: no fill at all (gridlines show through)
Range("A1").Font.ColorIndex = xlColorIndexAutomatic     ' -4105: automatic (black on white, per theme)

xlNone is the correct way to remove a fill — genuinely empty, not painted white (a white fill is still a fill; see VBA Cell Color). xlColorIndexAutomatic is the font default: it means "whatever Excel's automatic color is," which follows the theme and flips sensibly on dark backgrounds, where a hard-coded RGB(0,0,0) would not. Neither has a .Color equivalent, so this is the one place ColorIndex is not just legacy baggage — it is the only tool for the job.

Reading a color back: ColorIndex is lossy

Reading is where the two spaces bite you a second time. If a cell was filled with an absolute color, asking for its ColorIndex does not give you back a clean answer — Excel returns the nearest palette slot, which throws away the exact color:

Range("A1").Interior.Color = RGB(200, 215, 240)   ' a specific soft blue
Debug.Print Range("A1").Interior.ColorIndex        ' a nearby slot number - NOT your color
Debug.Print Range("A1").Interior.Color             ' the exact 24-bit value you set

The rule for reading is the mirror of the rule for writing: read back with the same property you wrote with. If you set .Color, compare .Color (against an RGB(...), since it is stored BGR — see VBA RGB). Only test .ColorIndex when you specifically care about the slot — for example, If .Interior.ColorIndex = xlNone to detect an unfilled cell, which is the one read where ColorIndex is exactly right.

When ColorIndex is still the right tool

It is not "always avoid it." Reach for ColorIndex when:

  • You need xlNone or xlColorIndexAutomatic — there is no other way to say them.
  • You are matching a workbook that was built on the 56-color palette and you want your fills to move with its scheme on purpose.
  • You are reading a cell only to ask "filled or not" (= xlNone).

For everything else — a report that must look the same for every reader, a brand color, anything you will compare later — set .Color = RGB(...). The judgment is simple once the model is clear: a seat number is convenient inside one theater and meaningless outside it. Absolute color is the default; the palette index is a deliberate, narrow choice.

How ExcelMaster helps

The Color-versus-ColorIndex split is exactly the kind of detail that compiles, runs, and then produces a color you did not mean — 6 read as almost-black, a header that shifts shade on the client's copy, a ColorIndex read that quietly rounds your color to a palette slot. None of it errors; it just looks wrong later.

ExcelMaster lets you describe the color you want — "fill the header dark blue," "clear last run's highlights," "use our brand green" — and it writes .Color = RGB(...) for absolute colors that look the same everywhere, ColorIndex = xlNone when you mean remove the fill, and xlColorIndexAutomatic for font that should follow the theme. It stays in one number space per line, so the "why is my color off" bug never ships. You keep the workbook and the code.

Frequently asked questions

What is the difference between Color and ColorIndex in VBA?

.Color is an absolute 24-bit value — Interior.Color = RGB(255,255,0) is yellow on every machine. .ColorIndex is a slot number from 1 to 56 into the workbook's palette; Interior.ColorIndex = 6 is "whatever color sits in slot 6," which a customized palette or theme can change. They use different number ranges, so never assign one's value to the other — .Color = 6 is almost black, not slot 6.

Why does the same ColorIndex look different in another workbook?

Because ColorIndex is a lookup into that workbook's palette (ActiveWorkbook.Colors), and the palette can differ between files — a template may customize it, or theme colors may remap it. The index is stable but the color behind it is not. If a color must look identical for everyone, set an absolute .Color = RGB(...) instead, which never consults the palette.

How do I remove a cell fill with ColorIndex?

Set Range("A1").Interior.ColorIndex = xlNone. That removes the fill completely, so gridlines show through and nothing prints — unlike Interior.Color = RGB(255,255,255), which paints the cell white and is still a fill. xlNone (value -4142) is one of the two jobs .Color cannot do, which is why ColorIndex is worth keeping for it.

What is xlColorIndexAutomatic?

xlColorIndexAutomatic (value -4105) is the "automatic" color — for Font.ColorIndex it means the default text color that follows Excel's theme, typically black on a light background and adapting on dark ones. It is the safe default for font color because a hard-coded RGB(0,0,0) cannot adapt. There is no .Color equivalent, so you set it through ColorIndex.

Should I use ColorIndex or RGB for new VBA code?

Prefer .Color = RGB(...) for new code: it is an absolute color that looks the same in every workbook and on every machine, and it covers all 16 million colors rather than 56 slots. Reserve ColorIndex for the specials xlNone and xlColorIndexAutomatic, for matching a workbook deliberately built on the legacy palette, and for the "is this cell filled" read (= xlNone).

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-08.

Related guides: VBA RGB · VBA Conditional Formatting · VBA Cell Color · VBA Font · VBA Borders