Excel Color Index Formula

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

overview image of 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.

color palette in Excel

  • 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.

finding color index value from Excel

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 through the developer tab to open the visual basic

  • Navigate to the Insert menu and choose Module.

adding module in VBA window

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

using code to build custom functions in Excel

Code Breakdown:

  • GetHEXcode(FCell As Range) defines a function named GetHEXcode that takes a cell range (FCell) and returns a string.
  • Dim xColor declares a string variable named xColor.
  • xColor = Right(“000000” & Hex(FCell.Interior.Color), 6) converts the interior color of the cell to a hexadecimal representation and assigns it to xColor. Leading zeros are added to ensure a 6-character length.
  • Meanwhile, the next line rearranges the characters in xColor to obtain the final hexadecimal color code and assigns it to GetHEXcode.
  • In the Excel sheet, enter the following formula in cell D5:
=GetHEXcode(B5)

using a custom formula to get the hex code for the color in Excel

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

using fill handle feature of Excel for quick output

  • 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

 

using code to drive RGB value of color in Excel

Code Breakdown:

  • The GetRGBvalue function takes a cell range (cellRange) and a color format (ColorScheme) as inputs and returns a variant.
  • It declares a variant variable named ColorCode to store the interior color of the specified cell.
  • The ColorCode variable is assigned the interior color of the cell specified by cellRange.
  • The code uses a Select Case statement to check the value of the ColorScheme in lowercase.
  • If ColorScheme is “index“, the function assigns the interior color index of Rng to the GetRGBvalue variable.
  • If ColorScheme is “rgb“, the code calculates the RGB values (Red, Green, Blue) from ColorCode and assigns them as a concatenated string to GetRGBvalue.
  • If ColorScheme is neither “index” nor “rgb“, the function assigns an error message indicating the appropriate format to be used.

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

=GetRGBvalue(B5,"rgb")

using custom function to get the RGB value

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:

=GetRGBvalue(B15,"index")

using custom function to get the index color value in Excel

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

NOTE: The index value assigned to colors in Excel is determined by their position in the predefined color palette. Therefore, certain colors, such as red and dark red, light blue and pale blue, or dark green and green, may share the same index number due to their similar placement in the palette.

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

using code to make a custom function for deriving decimal values of colors

Code Breakdown:

  • The GetDecimal function takes a cell range (FCell) and an optional parameter (Opt) as inputs and returns a Long value.
  • Several Long variables (xColor, R, G, B) are declared to store color-related values.
  • The xColor variable is assigned the interior color of the specified cell (FCell).
  • The R, G, and B variables are calculated by extracting the corresponding color values (Red, Green, Blue) from xColor using modulus and division operations.
  • In the Select Case statement, if Opt is 1, 2, or 3, the function assigns the corresponding color value (Red, Green, or Blue) to GetDecimal; otherwise, it assigns the original color value (xColor).
  • Enter the function in cell D5 to obtain the decimal value of the color in cell B5:
=GetDecimal(B5)

decimal value of color dark red

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

finding decimal values of color code using a custom function


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.

using name range to create a new name range

  • Assign a name to the named range (here, “background“).
  • In the Refers to box, enter the following formula to retrieve the index color:
=GET.CELL(63,INDIRECT("rc[-2]",FALSE))

name range window

Formula Breakdown:
This formula instructs Excel to fetch the index color of the cell that is 2 columns to the left of the current cell using the GET.CELL function with the specified info_type (63 for index color)
  • Enter the following formula in cell D5:
=Background

retrieving color index

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

final output from using GET.CELL function

  • 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

using code to extract font color index in Excel

Code Breakdown:

  • The code is a custom function called FontIndex that returns the index value of the font color for a given range of cells.
  • It uses the property VarRange.Font.ColorIndex to retrieve the font color index in particular.

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:
=FontIndex(C15)

final output from a custom function


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


<< Go Back to Excel Get Cell ColorExcel Cell FormatLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Ishrak Khan
Ishrak Khan

Qayem Ishrak Khan, BURP, Urban and Regional Planning, Chittagong University of Engineering and Technology, Bangladesh, has been working with the ExcelDemy project for 1 year. He wrote over 40+ articles for ExcelDemy. He is an Excel and VBA Content Developer providing authentic solutions to different Excel-related problems and writing amazing content articles regularly. Data Visualization, DBMS, and Data Analysis are his main areas of interest. Besides, He has passions about learning and working with different features of Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo