How to Find Color Index in Excel

In this article, we are going to see how to find the color index in Excel of the cell background and the cell fonts using a custom user-defined function. We will show how to find the color index in Excel in different code formats such as long decimal integer, RGB value, and also defaults color codes.

Detecting the color code or the color index of the cell background or cell font is one of the most common things analysts do. Color coding results and then detecting them are done to increase the clarity of the operations and their understandability.

Below we showed how we can find a color index in Excel in the Hex color or the long format of the font color.

Download Practice Workbook

Download the following workbook to practice by yourself.

How to Find the Long Integer Color Index

In order to find the color code of the background of the cell alongside the font, we can use the below VBA code.

Function ColorCodeLongBackground(cell As Range) As Long
If cell Is Nothing Then
ColorCodeLongBackground = -1
Else
ColorCodeLongBackground = cell.Interior.Color
End If
End Function
Function ColorCodeLongFont(cell As Range) As Long
If cell Is Nothing Then
ColorCodeLongFont = -1 ' Return -1 if the cell reference is invalid
Else
ColorCodeLongFont = cell.Font.Color ' Get the color code of the cell's font
End If
End Function

• After we create the custom function, we can use it to get the color code of the background cell in the range of cells B5:B14.
• For this, enter the following formula in cell C5.

• After we create the custom function for the font, we can use it to get the color code of the fonts in the range of cells C5:C14.
• For this, enter the following formula in cell C5,

How to Find the RGB Color Index of Cell

In order to find the color code of the background of the cell alongside the font, we can use the below VBA code.

Function ColorCodeRGBBackground(cell As Range) As String
    If cell Is Nothing Then
        ColorCodeRGBBackground = "Invalid Cell"
    Else
        Dim colorValue As Long
        colorValue = cell.Interior.Color
        Dim redValue As Integer
        Dim greenValue As Integer
        Dim blueValue As Integer
        redValue = colorValue Mod 256
        greenValue = (colorValue \ 256) Mod 256
        blueValue = (colorValue \ 65536) Mod 256
        ColorCodeRGBBackground = "RGB(" & redValue & ", " & greenValue & ", " & blueValue & ")"
    End If
End Function
Function ColorCodeRGBFont(cell As Range) As String
    If cell Is Nothing Then
        ColorCodeRGBFont = "Invalid Cell"
    Else
        Dim colorValue As Long
        colorValue = cell.Font.Color
        Dim redValue As Integer
        Dim greenValue As Integer
        Dim blueValue As Integer
        redValue = colorValue Mod 256
        greenValue = (colorValue \ 256) Mod 256
        blueValue = (colorValue \ 65536) Mod 256
        ColorCodeRGBFont = "RGB(" & redValue & ", " & greenValue & ", " & blueValue & ")"
    End If
End Function

• After we create the custom function, we can use it to get the color code of the background cell in the range of cells B5:B14.
• For this, enter the following formula in cell C5.

• After we create the custom function for the font, we can use it to get the color code of the fonts in the range of cells C5:C14 in RGB. 
• For this, enter the following formula in cell C5,

How to Find the Color Index Based on the Standard Color Index

The color code constant is a set of constant values allotted to Excel. There are normally 56 color-coded index values in the library.

• For this, we are going to create a VBA code that will include two separate functions.
• Open the VBA code following this helper article.
• And then enter the following code in the code editor.

Function ColorCodeStandard56Background(cell As Range) As Long
    ColorCodeStandard56Background = cell.Interior.colorIndex
End Function
Sub TestColorCodeStandard56Background()
    Dim cell As Range
    Set cell = ActiveSheet.Range("A1")
    Dim colorIndex As Long
    colorIndex = ColorCodeStandard56Background(cell)
    If colorIndex <> xlNone Then
        MsgBox "The color index of the cell background is: " & colorIndex
    Else
        MsgBox "The cell has no background color."
    End If
End Sub
Function ColorCodeStandard56Font(cell As Range) As Long
    ColorCodeStandard56Font = cell.Font.colorIndex
End Function
Sub TestColorCodeStandard56Font()
    Dim cell As Range
    Set cell = ActiveSheet.Range("A1")
    Dim fontColorIndex As Long
    fontColorIndex = ColorCodeStandard56Font(cell)
    If fontColorIndex <> xlColorIndexNone Then
        MsgBox "The color index of the cell font is: " & fontColorIndex
    Else
        MsgBox "The cell font has no color."
    End If
End Sub

• After we create the custom function, we can use it to get the color code of the background cell in the range of cells B5:B14.
• For this, enter the following formula in cell C5.

• After we create the custom function for the font, we can use it to get the color code of the fonts in the range of cells D5:D14 in RGB
• For this, enter the following formula in cell E5,

How to Find the Color Code Index of Cell in System Color Format

In Excel, there are a couple of fixed color codes available. The existence of those ensures the smoothness of the coding procedure.

• First, we need to open the VBA editor using the helper article given here.
• Then enter the following code in the code editor.

Function GetSystemColorName(colorIndex As Long) As String
    Select Case colorIndex
        Case 1
            GetSystemColorName = "vbBlack"
        Case 2
            GetSystemColorName = "vbWhite"
        Case 3
            GetSystemColorName = "vbRed"
        Case 4
            GetSystemColorName = "vbGreen"
        Case 5
            GetSystemColorName = "vbBlue"
        Case 6
            GetSystemColorName = "vbYellow"
        Case 7
            GetSystemColorName = "vbMagenta"
        Case 8
            GetSystemColorName = "vbCyan"
        Case 9
            GetSystemColorName = "vbGray"
        Case 10
            GetSystemColorName = "vbGrey"
        Case Else
            GetSystemColorName = "Unknown"
    End Select
End Function

Function ColorCodeSystemBackground(cell As Range) As String
    Dim colorIndex As Long
    colorIndex = cell.Interior.colorIndex
    ColorCodeSystemBackground = GetSystemColorName(colorIndex)
End Function

Function ColorCodeSystemFont(cell As Range) As String
    Dim fontColorIndex As Long
    fontColorIndex = cell.Font.colorIndex
    ColorCodeSystemFont = GetSystemColorName(fontColorIndex)
End Function

Sub TestColorCodeSystem()
    Dim cell As Range
    Set cell = ActiveSheet.Range("A1")
    Dim bgColorResult As String
    bgColorResult = ColorCodeSystemBackground(cell)
    Dim fontColorResult As String
    fontColorResult = ColorCodeSystemFont(cell)
    MsgBox "Cell background color: " & bgColorResult & vbCrLf & _
           "Cell font color: " & fontColorResult
End Sub

• After we have created the functions, we can use them to get the color codes in a constant shape format.
• For this, enter the following formula in cell

• After we have created the functions, we can use them to get the color codes in constant shape format fonts in the range of cell E5:E12.
• For this, enter the following formula in cell

Things to Remember

Color index vs. RGB values: Excel uses a system of color indexing to represent colors in cells, rather than the more common RGB (Red-Green-Blue) values. Each color index corresponds to a specific color in the Excel color palette.

Limited color palette: Excel has a limited color palette, typically consisting of 56 color indexes (1 to 56), although the number may vary depending on the version of Excel you are using. Each color index represents a different color.

Conditional Formatting: If you want to apply specific formatting based on color, you can use Conditional Formatting to set rules based on color index values.

VBA: If you want to work with color indexes programmatically or perform advanced color-related tasks, you can use VBA (Visual Basic for Applications) code to access and manipulate color indexes.

Color Index Values: Excel’s color index values are not consistent across different versions and platforms. The default color palette may vary, so it’s essential to verify the color indexes for your specific version of Excel.

Changing Color Index: While you can set a cell’s color using color index values, be aware that changing the color palette or theme in Excel might alter the appearance of the colors in your spreadsheet.

Use color picker: Excel does not provide a built-in feature to directly see the color index value of a cell’s color. Instead, you can use a color picker tool or VBA code to determine the color index of a specific color.

RGB to Color Index Conversion: If you have an RGB color value that you want to convert to a color index, you’ll need to use VBA code or a lookup table to find the closest matching color index in the Excel palette.

Frequently Asked Questions

Q1. How do I find the color of text?

In Microsoft Excel, you can find the color of the text (font color) using the following steps:

Select the cell or range of cells containing the text whose color you want to find.

In the “Home” tab on the Excel ribbon, look for the “Font Color” button in the “Font” group. It typically looks like a letter “A” with a colored bar underneath it.

Click on the drop-down arrow next to the “Font Color” button. This will open a color palette showing the currently applied font color.

The color that is selected in the color palette represents the font color of the selected text.

Q2. Can you search for colored text in Excel?

Yes, you can search for colored text or colored backgrounds in Excel. For this, you need to use the Find and Replace tool. In that tool, select the cell color and font color from the format tab. Or you can select the desired format from a cell by picking up the cell format. After that, by simply pressing the find button, you can find the cells with the desired results.

Q3. How do I sum colored cells in Excel without VBA?

Press Ctrl + F to open the “Find” dialog box.

Click the “Options” button to expand the dialog box.

Go to the “Format” button and choose the “Fill” tab. Select the color you want to sum up and click “Find All.”

Press Ctrl + A to select all the found cells. Then click “Sum” at the bottom of the Excel window to see the sum of the selected cells.

Conclusion

In this article, we can see how we can find a color index of the cell background and the font. We can extract the color code of the cell background and the cell font in various formats. Like the RGB format, Long Integer, Constant, and also in system color format. Users need to understand their needs and extract color index according to that.