Create a colorindex table

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:

This is the result:

Here is the code I used:

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

3 Responses to “Create a colorindex table”

  1. Very nice. This will come in handy when trying to reference, especially when changing the default colors.

  2. Excellent. This will be very useful when setting the Font.ColorIndex = in an Excel macro I use often.

  3. Thanks!

Pinterest