I'm working on an ASAP Utilities User Guide. When describing some of the worksheet function that use the colorindex property, I needed a good example of all available colors.
So I wrote a quick macro to create a colorindex-table but enhanced it to show the colors in the same order as it is shown in the dropdown box:
Sub ColorTable()
' variables Dim i
As Integer Dim j
As Integer Dim k
As Integer Dim sColorOrder
As String Dim sLightColors
As String Dim arColorOrder
As Variant Dim iColorNr
As Integer i = 0
' these are the colors in de same order Excel shows ' them in the pulldown: sColorOrder = "1,53,52,51,49,11,55,56,9,46,12,10,14," & _
"5,47,16,3,45,43,50,42,41,13,48,7,44,6," & _
"4,8,33,54,15,38,40,36,35,34,37,39,2,17," & _
"18,19,20,21,22,23,24,25,26,27,28,29,30,31,32"
arColorOrder = Split(sColorOrder, ",", , vbTextCompare)
' Light colors that will have a dark fontcolor: sLightColors = "|6|36|19|27|35|20|28|8|34|2|"
Application.ScreenUpdating =
False For j = 1
To 7
' loop rows For k = 1
To 8
' loop columns With Cells(j, k)
iColorNr = arColorOrder(i)
.Interior.ColorIndex = iColorNr
.Value = iColorNr
' is the color light, then make the textcolor darker If InStr(1, sLightColors, "|" & iColorNr & "|") > 0
Then .Font.ColorIndex = 56
'dark grey Else .Font.ColorIndex = 2
'white End If End With i = i + 1
Next k
Next j
' Give it a nice layout:
With Range(Cells(1, 1), Cells(7, 8))
.RowHeight = 20
.ColumnWidth = 4
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
End With
Application.ScreenUpdating = True
End Sub