Excel VBA ColorIndex

The VBA ColorIndex property in Excel sets fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, plus -4105 and -4142. Use enumeration -4105 (xlColorIndexAutomatic) for default colors, and -4142 (xlColorIndexNone) to clear colors or set no color.

Syntax of Excel VBA ColorIndex

You can set or return the color index value of Excel objects using the following syntax.

expression.ColorIndex

To get the ColorIndex value of an Excel font, interior or border color and store it in a variable:

dblColorValue = expression.ColorIndex

To set the ColorIndex value on an Excel color object:

expression.ColorIndex = IndexValue   ' 1 to 56, -4105 or -4142

ColorIndex in Excel VBA

Here are all 56 Excel VBA ColorIndex values with their corresponding colors and RGB equivalents.

VBA to print the ColorIndex table

This macro prints all 56 ColorIndex values and their colors into an Excel sheet, so you can see the palette in your own workbook.

Sub sbExcel_VBA_PrintColorIndex()
    rowCntr = 2
    colCntr = 2

    For iCntr = 1 To 56

        Cells(rowCntr, colCntr).Interior.ColorIndex = iCntr
        Cells(rowCntr, colCntr) = iCntr

        If iCntr > 1 And iCntr Mod 14 = 0 Then
            colCntr = colCntr + 1
            rowCntr = 2
        Else
            rowCntr = rowCntr + 1
        End If

    Next

End Sub

Set ColorIndex in Excel VBA

These macros set the ColorIndex on a range of cells in an Excel worksheet.

Font colors in Excel VBA

Set font colors using the ColorIndex property of the Font object. This Excel VBA font color macro sets the font color of the range A1:E20.

Sub SetFontColorIndex_Range()
    Range("A1:E20").Font.ColorIndex = 40
End Sub

You can also read the font color into a variable:

myVar = Range("A1").Font.ColorIndex

Interior colors in Excel VBA

Change the interior or fill color of a range using the ColorIndex property. This Excel interior color macro changes the fill color of an object.

Sub SetInteriorColorIndex_Range()
    Range("A1:E20").Interior.ColorIndex = 41
End Sub

To get a cell color in Excel VBA, read the interior ColorIndex back:

myVar = Range("A1:E20").Interior.ColorIndex

Border colors in Excel VBA

The ColorIndex property of the Borders object sets border colors.

Sub SetBordersColorIndex_Range()
    Range("A1:E20").Borders.ColorIndex = 42
End Sub

Get cell color in Excel VBA

Reading a color back is the mirror of setting one. The property you read determines what you get: ColorIndex returns a number from 1 to 56, while Color returns a long integer holding the full RGB value.

Sub GetCellColor()
    Dim idx As Long, clr As Long

    idx = Range("A1").Interior.ColorIndex   ' 1 to 56, or -4142 if no fill
    clr = Range("A1").Interior.Color        ' long integer, e.g. 255 for red

    MsgBox "ColorIndex: " & idx & vbNewLine & "Color: " & clr
End Sub

Get the red, green and blue values of a cell

The Color property returns a single long integer, not three separate numbers. VBA stores it as BGR rather than RGB, so extract the channels like this:

Sub GetCellRGB()
    Dim clr As Long, R As Integer, G As Integer, B As Integer

    clr = Range("A1").Interior.Color

    R = clr Mod 256
    G = (clr \ 256) Mod 256
    B = (clr \ 65536) Mod 256

    MsgBox "R=" & R & "  G=" & G & "  B=" & B
End Sub

Get the cell color of a range

Reading ColorIndex from a multi-cell range returns a value only if every cell shares the same color. If they differ you get Null, so test for it before using the result.

Sub GetRangeColor()
    Dim v As Variant

    v = Range("A1:E20").Interior.ColorIndex

    If IsNull(v) Then
        MsgBox "Mixed colors in the range"
    Else
        MsgBox "All cells use ColorIndex " & v
    End If
End Sub

Count cells by color

Excel has no built-in function for this, which is why it is one of the most common reasons people reach for VBA. Use it in a worksheet as =CountByColor(A1:E20, G1).

Function CountByColor(rng As Range, refCell As Range) As Long
    Dim c As Range, n As Long

    For Each c In rng
        If c.Interior.Color = refCell.Interior.Color Then n = n + 1
    Next c

    CountByColor = n
End Function

Clear colors in Excel VBA

Sometimes you need no fill at all. These macros clear font, border and fill colors, setting them back to automatic or none.

Clear background color in Excel VBA

Use this macro to clear the background color and set no interior fill.

Sub SetClearBackgroundColor_ColorIndex_Range()
    Range("A1:E20").Interior.ColorIndex = xlNone
    'OR
    'Range("A1:E20").Interior.ColorIndex = -4142
End Sub

Interior.ColorIndex = -4142 is the enumeration behind xlNone, which clears the background or fill color.

Border colors clear the same way:

Sub SetClearBorders_ColorIndex_Range()
    Range("A1:E20").Borders.ColorIndex = -4142
End Sub

Font colors reset to automatic with -4105 rather than -4142:

Sub SetClearFontColorIndex_Range()
    Range("A1:E20").Font.ColorIndex = -4105
End Sub

VBA colors

There are three ways to set colors in VBA: the ColorIndex property, VBA color constants, and RGB values. ColorIndex is covered above — here are the other two.

Excel VBA color constants

The built-in color constants are the quickest option when one of the eight basic colors will do.

Sub sbExcel_VBA_ColorConstants()
    Cells(2, 4).Interior.Color = vbBlack
    Cells(3, 4).Interior.Color = vbRed
    Cells(4, 4).Interior.Color = vbGreen
    Cells(5, 4).Interior.Color = vbYellow
    Cells(6, 4).Interior.Color = vbBlue
    Cells(7, 4).Interior.Color = vbMagenta
    Cells(8, 4).Interior.Color = vbCyan
    Cells(9, 4).Interior.Color = vbWhite
End Sub

RGB colors in Excel VBA

Constants and ColorIndex give you a limited palette. RGB lets you use any combination of red, green and blue — 0 to 255 on each channel, so roughly 16.7 million colors.

Sub ChangeBackgroundColorRGB_Range()
    Range("A1:E20").Interior.Color = RGB(125, 205, 99)
End Sub

Note that RGB values are assigned to .Color, not .ColorIndex. Mixing the two is the most common source of unexpected results when setting colors in VBA.

Hex color codes in Excel VBA

VBA has no direct hex color input, so a hex code from a brand guide or a design tool has to be converted. There are two ways.

Using the RGB function

Split the hex into its three pairs and pass them as decimals. #7DCD63 becomes 7D, CD, 63 — that is 125, 205, 99.

Range("A1").Interior.Color = RGB(125, 205, 99)

Using a hex literal directly

VBA accepts hex with the &H prefix, but the byte order is reversed — BGR, not RGB. So #7DCD63 is written as &H63CD7D.

Range("A1").Interior.Color = &H63CD7D

Convert any hex code with a function

This function handles the reversal for you, so you can paste hex codes straight from a design spec.

Function HexToColor(hexCode As String) As Long
    Dim h As String
    h = Replace(hexCode, "#", "")

    HexToColor = RGB(CLng("&H" & Mid(h, 1, 2)), _
                     CLng("&H" & Mid(h, 3, 2)), _
                     CLng("&H" & Mid(h, 5, 2)))
End Function

Sub UseHexColor()
    Range("A1").Interior.Color = HexToColor("#7DCD63")
End Sub

Free download: ColorIndex example workbook

One macro-enabled workbook containing the palette, a hex converter and every macro from this page, ready to run. Free, no sign-up.

Macros included

The module is called ColorIndexExamples. Press Alt + F11 to open it, click inside any procedure and press F5 to run.

Download the ColorIndex example workbook (.zip)

Frequently asked questions

Related Excel VBA guides

• 100+ most useful Excel VBA codes — the full macro library.
• VBA Code Explorer — browse by object, method and property.
• Excel chart VBA examples — including chart series colors.
• 100+ VBA interview questions — with worked answers.
• Excel VBA tutorial — from basics to advanced programming.