Excel Color Index Formula

In this article, we will explain how to use the Excel color index formula.

One of the standout features of Excel Color Index is its extensive library of 56 unique predefined color code options.

To find the color index in Excel, simply:

• Right-click on any cell.
• Select Format Cells from the Format Cells dialog box.
• Navigate to the Fill tab.

The default, predefined color palette will be displayed.

• For additional color choices, select the “More Colors” option.
• In the Custom tab of the Colors window, you can find the RGB and HEX values for any color.

In Microsoft Excel, there is no specific built-in function or formula available to directly obtain the color index of a cell. However, you can access and retrieve the color index by utilizing Visual Basic for Applications (VBA) code. Using the same process, we can obtain the hex code, RGB value, and index numbers of colors in Excel.

Case 1 – Finding Hex Color Code

STEPS:

• To access the Visual Basic for Applications (VBA) editor in Excel, go to the Developer tab.
• Select the Visual Basic option, which will open a new window for VBA.

• Navigate to the Insert menu and choose Module.

A new white page will appear on the left side of the window.

• Enter the following custom code:

Function GetHEXcode(FCell As Range) As String
'UpdatebyExceldemy
Dim xColor As String
xColor = CStr(FCell.Interior.Color)
xColor = Right("000000" & Hex(xColor), 6)
GetHEXcode = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
End Function

• In the Excel sheet, enter the following formula in cell D5:

The formula calculates the hex color code for the fill color of cell B5, which is dark red, and returns the value C00000.

• Use the fill handle to extend the formula to the remaining cells in the dataset. This allows us to obtain the hex value for all the colors in a quick and efficient manner.

Case 2 – Finding RGB Color Codes

STEPS:

• Access the VBA module through the Developer tab as above.
• Enter the following code in the module:

Function GetRGBvalue(cellRange As Range, ByVal ColorScheme As String) As Variant
    Dim ColorCode As Variant
    ColorCode = Cells(cellRange.Row, cellRange.Column).Interior.Color
    Select Case LCase(ColorScheme)
        Case "index"
            GetRGBvalue = cellRange.Interior.ColorIndex
        Case "rgb"
            GetRGBvalue = (ColorCode Mod 256) & ", " & _
            ((ColorCode \ 256) Mod 256) & ", " & (ColorCode \ 65536)
        Case Else
            GetRGBvalue = "Use either 'Index' or 'RGB' as second argument."
    End Select
End Function

In the cell range from D5 to D15, we will apply the following formula to get the RGB color values:

By applying the custom formula in cell D15, we can retrieve the RGB color value of the black cell (B15), which is 0,0,0.

By using the same formula and replacing “RGB” with “index“, the index value of the color code can be returned.

In the following image, we used the following formula in cell D15 to get the index value of the color of cell B15:

The function successfully returned the index value for the color black.

Case 3 – Finding Decimal Values of Color Code

STEPS:

• As above, go to the Developer Tab and select Visual Basic.
• Enter the following code to make a custom function:

Function GetDecimal(FCell As Range, Optional Opt As Integer = 0) As Long
'Updateby _ Exceldemy
Dim xColor As Long
Dim R As Long, G As Long, B As Long
xColor = FCell.Interior.Color
R = xColor Mod 256
G = (xColor \ 256) Mod 256
B = (xColor \ 65536) Mod 256
Select Case Opt
Case 1
GetDecimal = R
Case 2
GetDecimal = G
Case 3
GetDecimal = B
Case Else
GetDecimal = xColor
End Select
End Function

• Enter the function in cell D5 to obtain the decimal value of the color in cell B5:

• Use the fill handle to quickly obtain the decimal values for the entire dataset.

How to Use GET.CELL Function in an Excel Formula to Get Cell Color Index

The GET.CELL function is a built-in function that offers a unique way to retrieve specific information about cells, including index color. To demonstrate its application, we will use the same dataset.

STEPS:

• Navigate to the Formulas tab and select Name Manager from the Defined Names group.
• in the Name Manager window that opens, click on New to create a new named range.

• Assign a name to the named range (here, “background“).
• In the Refers to box, enter the following formula to retrieve the index color:

• Enter the following formula in cell D5:

The formula retrieves the color index of cell B5, which is located two columns to the left of the current cell D5.

• Drag the fill handle to apply the formula to the desired cell range or dataset, allowing you to obtain the color index for each corresponding cell.

How to Get Font Color Index in Excel

STEPS:

• Press the keyboard shortcut ALT+F11 to open the Visual Basic window.
• Enter the following code into the window:

Code
Function FontIndex(VarRange As Range) As Integer
FontIndex = VarRange.Font.ColorIndex
End Function

We can utilize the custom function in the dataset to obtain the font color index.

• In cell D15, enter the following formula to extract the font color index of cell C15:

Things to Remember

• Press ALT+F11 on your keyboard to open the VBA editor (Shortcut).
• RGB color values are widely used in a host of applications.
• The color index remains unchanged even if the cell color is modified. Press the F9 key to recalculate the formula.

Frequently Asked Questions

1. What is the Color index number?
The color index number in Excel represents the predefined index assigned to each color, with support for up to 56 color indexes, numbered from 1 to 56.

2. Can I customize the color index in Excel?
No, the color index in Excel is predefined and cannot be customized. However, you can choose from the available colors in the Excel palette to assign specific color indices to cells.

3. How do I read cell color in Excel?
Reading cell colors in Excel requires the use of VBA code or other programming methods, as Excel does not have a built-in function for directly retrieving cell colors.

4. Are color indexes consistent across different versions of Excel?
Yes, color indexes in Excel are generally consistent across different versions, however the available colors may vary. Additionally, custom color indexes may not be consistent if a workbook is opened in a different version of Excel.

5. Is there a way to convert the color index to RGB values?
Yes, you can convert the color index to RGB (red, green, and blue) values using various methods such as VBA code, provided that this conversion allows you to determine the specific RGB color values associated with a particular color index in Excel.

Download Practice Workbook

Related Articles

• If Cell Color Is Red Then Execute Different Functions in Excel
• Excel If Cell Color Is Green Then Show or Customize Outputs
• VBA to Check If Font Color is Red Then Return Results in Excel

<< Go Back to Excel Get Cell Color | Excel Cell Format | Learn Excel