Excel VBA Color Index List

Excel color index are used to change either a worksheet’s background(Interior), font or border color.

There are 56 colors that has predefined color index in Excel. These base codes are present in the Excel Color palette. To get the absolute VBA color code for any colour that we have chosen (datatype: long) from palette, we have to use interior color property.

This can be referred & used in a VBA code as explained in below code snippets.

Excel VBA Color Index Table

You can create Excel Colorindex table using this VBA code. If you are trying to convert the VBA color code to different format, then check this site.

The below code can be used to create a Color index table, get Color index of a cell background, border or font and also to set the color.

Sub Excel_VBA_Color_Index_List()
    Dim idxColor As Integer
    
    'There are 56 Colors added to the Excel Color Index Table Palette
    For idxColor = 1 To 56
        'Set Color Index to a Excel Worksheet Cell
        ThisWorkbook.Sheets(1).Cells(idxColor, 1).Interior.ColorIndex = idxColor
        ThisWorkbook.Sheets(1).Cells(idxColor, 2) = ThisWorkbook.Sheets(1).Cells(idxColor, 1).Interior.Color
        
        'Get the Color Index of Cell Background, Border & Font
        ThisWorkbook.Sheets(1).Cells(idxColor, 3) = ThisWorkbook.Sheets(1).Cells(idxColor, 1).Interior.ColorIndex
        ThisWorkbook.Sheets(1).Cells(idxColor, 4) = ThisWorkbook.Sheets(1).Cells(idxColor, 1).Borders.ColorIndex
        ThisWorkbook.Sheets(1).Cells(idxColor, 5) = ThisWorkbook.Sheets(1).Cells(idxColor, 1).Font.ColorIndex
    Next idxColor
    
End Sub

Execute the above code by pressing F5 on a new workbook.

Predefined Names For VBA Color Index List

In VBA code, there 8 color constants that are predefined. They are listed below.

  1. vbBlack
  2. vbBlue
  3. vbCyan
  4. vbGreen
  5. vbMagenda
  6. vbRed
  7. vbWhite
  8. vbYellow

These values hold the long (decimal) value corresponding to VBA color index. In case if you want more color constants, then use the color codes defined under ‘XlRgbColor’ collection.

'Assigning Color codes with Predefined VB Names
ThisWorkbook.Sheets(1).Cells(1, 3).Interior.Color = rgbFireBrick ‘or
ThisWorkbook.Sheets(1).Cells(2, 3).Interior.Color = vbGreen

Instead of using numbers inside code, this way of naming the colors will be more meaningful to the programmers. It is easy to understand.

Get Complete List of Color Code Names

If you want to see a complete list of predefined names for colors, then go to Excel Vb editor, press F2 to get ‘Object browser.

You can find a search box in this object browser. Type “Colorconstants” or “xlrgb color” & press enter.

This will give you complete list of predefined colors. Inside any vba code, you can just use these names instead of worrying about color codes.

Excel VBA Color Index Predefined Names
Excel VBA Color Index Predefined Names

Similar to this, you can view ‘colorconstants’ starting with vb (like vbgreen, vbred etc.,)

Change Worksheet Tab Color

We an also set a background color for each worksheet name Tabs in Excel. At the left bottom of Worksheet, you an see different Tabs with worksheet names present in the active Excel workbook.

Using the below code, the Worksheet tab color can be set or modified.

Sub Change_Tab_Color()
    'Set Background Color of Tabs for Different WorkSheet Names

    ThisWorkbook.Sheets(1).Tab.Color = rgbDarkBlue
    ThisWorkbook.Sheets(2).Tab.Color = vbGreen

End Sub

Once F5 key is pressed, the above code executes & changes the Tab colors. This will be easy to color code tabs for easy identification.

Leave a Reply