There are two ways of setting colors of Cell Backgrounds, Fonts or Borders in Excel. One of them is the VBA ColorIndex property and the other of them is the VBA Color property. But what is the difference between the two, and which is better! So let us explore…
The VBA ColorIndex property can be any value between 1 and 56, and represents a color defined within your currently selected color theme with the provided index. As you can see in the image on the left this represents a variety of colors. The downside of using the ColorIndex is that it is dependent on the actually selected color theme and you are limited to selecting colors from the 56 color palette.
If you want to print a similar ColorIndex table simply use the code below in any selected worksheet:
Sub PrintColorIndexTable()
For i = 1 To 56
With Cells(Int((i - 1) / 8) + 1, (i - 1) Mod 8 + 1)
.Interior.ColorIndex = i
.Value = i
End With
Next i
End Sub
VBA ColorIndex example
On the left you can see several examples of setting the VBA ColorIndex to different Excel Range properties such as Interior (background), Font and Borders.
The VBA Color property
The VBA Color property is a more convenient way of defining the color of a Cell Interior, Border or Font. The Excel VBA Color property accepts Long values representing a RGB color. The easiest way to set this property is to use the VBA RGB function:
Example: Setting cell background to red
Range("A1").Interior.Color = RGB(255, 0, 0)
Example: Setting cell font to blue
Range("A1").Font.Color = RGB(0, 0, 255)
Example: Setting cell borders to yellow
Range("A1").Borders.Color = RGB(255, 255, 0)
Summary
Using colors is easily achieved in Excel VBA. The Excel VBA ColorIndex property limits you however to 56 theme or default Excel colors and is rarely used in practice. The Excel VBA Color on the other side allows you to set any color to any Excel Cell property using the VBA RGB function.
Thank you for a well done article. Clean and accurate.
I DO use ColorIndex because it has been my belief that it is (with one caveat) reliable, in that if two cells had .Interior.ColorIndex 45, 1. If in the same workbook, their background/fill would look the same; and 2. whether in the same WB or not, both would be reddish orange*(as you show), UNLESS a non default palette was chosen for the workbook (that’s the caveat). If another palette had been set, 45 could be green or purple or anything.
* (On Office 2016 it’s orange on the standard palette. Microsoft, to their Satanic discredit, has altered the 1 through 56 ColorIndex appearances on version changes, at least on 2003 and 2007 and I believe 2010 and possibly 2013 and 2016. Yet yet yet another Special Place In Hell action by the Excel or Office developers.)
So while Colorindex is limited to 56 values, at least I thought that item 1. held reliably. Today I learned that it does not. As to item 2. one day I hope to enhance WorkbookOpen to detect a nonstandard palette and give a msgbox, as I already do when I see 1904 system. That way at least I would be pre-warned that someone made Colorindex 45 be purple.
What happened today was that I set Fill(Interior) from clicking a light brown choice on the standard interface (keyboard Control-1, “fill” tab). That cell’s interior now has ColorIndex 45, Color 36799 and looks light brown. Another cell had colorindex set manually (such as your displaying code) to 45 and looks orange. ITs interior has ColorIndex 45, Color 39423 and looks orange.
So I have two cells on the same sheet that both have ColorIndex 45 and are wildly different.
I guess what’s happening is that they left a Bogus ColorIndex setting when you change the color – i.e., 45. But what is undeniably mentally inept (as Microsoft development frequently is) is that I picked this from the short table of preset colors under the Fill tab (hitting Control-1 with a cell highlighted, or the ribbon Fill Color). Why aren’t THOSE the 56 ColorIndex items? I could see them becoming befuddled had I used a custom color, say an exotic blend of R, G and B. But these are the PRESETS. Why would they corrupt the value of ColorIndex when I chose one of the preset Fill colors?
And by the way those preset colors (on Office 2016) were set by a madman. About half of them are some form of gray. Only 1 form of red, 1 of purple, 1 of yellow. Yeah. They eliminated light yellow! And sky blue. In fact, over one fourth of them make regular black foreground font UNREADABLE. Like has been said one million and three times, it wasn’t broke, so why did they fix it – I mean, f… it up for NO beneficial reason? Special place.
Long comment here but content packed, not just a whine. I rant but only with respect to specific, actual real world reproducible, and utterly shameful examples of simple developer idiocy.
So where does this leave me? If Microsoft is willing to corrupt ColorIndex simply because you picked from the small set, THEIR small set of preset fill colors, I guess that ColorIndex is not just limited as you mention, but “conditionally” useless.
P.S. I don’t know if this is a bug since 2003 or what, but for what it’s worth, this really damages me. I have elaborate personal macros that look for, say, orange and red background cells (and even laboriously tests if a conditional fill is applicable and reports what the fill actually looks like)(originating from a Chip Pearson approach if you know that name). My logic checked a few specified ColorIndex values (for example, like you show 3, 44, 45, 46 and a few others) and gives them special treatment. How am I supposed to deal with literally millions of .Color values? By the way, I tried. At one point I had it as Array(255, 6697881, 8421631, 128, 52479, 39423, 26367, 13107, 13209, 6697881) but that was clunky and as noted before subject to Microsoft developers seemingly cluelessly or capriciously altering their assignments on Excel version changes.
As you can see in the image on the left this represents a variety of colors. The downside of using the ColorIndex is that it is dependent on the actually selected color theme and you are limited to selecting colors from the 56 color palette.
On the left you can see several examples of setting the VBA ColorIndex to different Excel Range properties such as Interior (background), Font and Borders.
Thank you for a well done article. Clean and accurate.
I DO use ColorIndex because it has been my belief that it is (with one caveat) reliable, in that if two cells had .Interior.ColorIndex 45,
1. If in the same workbook, their background/fill would look the same; and
2. whether in the same WB or not, both would be reddish orange*(as you show), UNLESS a non default palette was chosen for the workbook (that’s the caveat). If another palette had been set, 45 could be green or purple or anything.
* (On Office 2016 it’s orange on the standard palette. Microsoft, to their Satanic discredit, has altered the 1 through 56 ColorIndex appearances on version changes, at least on 2003 and 2007 and I believe 2010 and possibly 2013 and 2016. Yet yet yet another Special Place In Hell action by the Excel or Office developers.)
So while Colorindex is limited to 56 values, at least I thought that item 1. held reliably. Today I learned that it does not.
As to item 2. one day I hope to enhance WorkbookOpen to detect a nonstandard palette and give a msgbox, as I already do when I see 1904 system. That way at least I would be pre-warned that someone made Colorindex 45 be purple.
What happened today was that I set Fill(Interior) from clicking a light brown choice on the standard interface (keyboard Control-1, “fill” tab).
That cell’s interior now has ColorIndex 45, Color 36799 and looks light brown.
Another cell had colorindex set manually (such as your displaying code) to 45 and looks orange.
ITs interior has ColorIndex 45, Color 39423 and looks orange.
So I have two cells on the same sheet that both have ColorIndex 45 and are wildly different.
I guess what’s happening is that they left a Bogus ColorIndex setting when you change the color – i.e., 45.
But what is undeniably mentally inept (as Microsoft development frequently is) is that I picked this from the short table of preset colors under the Fill tab (hitting Control-1 with a cell highlighted, or the ribbon Fill Color). Why aren’t THOSE the 56 ColorIndex items? I could see them becoming befuddled had I used a custom color, say an exotic blend of R, G and B. But these are the PRESETS. Why would they corrupt the value of ColorIndex when I chose one of the preset Fill colors?
And by the way those preset colors (on Office 2016) were set by a madman. About half of them are some form of gray. Only 1 form of red, 1 of purple, 1 of yellow. Yeah. They eliminated light yellow! And sky blue. In fact, over one fourth of them make regular black foreground font UNREADABLE. Like has been said one million and three times, it wasn’t broke, so why did they fix it – I mean, f… it up for NO beneficial reason? Special place.
Long comment here but content packed, not just a whine. I rant but only with respect to specific, actual real world reproducible, and utterly shameful examples of simple developer idiocy.
So where does this leave me? If Microsoft is willing to corrupt ColorIndex simply because you picked from the small set, THEIR small set of preset fill colors, I guess that ColorIndex is not just limited as you mention, but “conditionally” useless.
P.S.
I don’t know if this is a bug since 2003 or what, but for what it’s worth, this really damages me. I have elaborate personal macros that look for, say, orange and red background cells (and even laboriously tests if a conditional fill is applicable and reports what the fill actually looks like)(originating from a Chip Pearson approach if you know that name). My logic checked a few specified ColorIndex values (for example, like you show 3, 44, 45, 46 and a few others) and gives them special treatment. How am I supposed to deal with literally millions of .Color values? By the way, I tried. At one point I had it as
Array(255, 6697881, 8421631, 128, 52479, 39423, 26367, 13107, 13209, 6697881)
but that was clunky and as noted before subject to Microsoft developers seemingly cluelessly or capriciously altering their assignments on Excel version changes.
Test?
I just submitted a comment but didn’t see “posted” or “awaiting moderation” or anything? So this test.