ExcelInsider Team
When working with cell colors in Excel, you might want to identify or replicate a color using VBA. Excel’s ColorIndex property assigns each color a number, which makes it easy to apply, compare, or copy cell colors using code.
In this article, you’ll learn several ways to find the ColorIndex of a cell in Excel using VBA. We’ll cover simple methods like reading the ColorIndex directly, looping through ranges, using a user-defined function (UDF), and displaying all available ColorIndex values. You’ll also learn how to apply and test ColorIndex values using a practical dataset.
Steps to display the ColorIndex of any currently selected cell with a quick VBA macro:
➤ Press Alt + F11 , insert a new module, and paste this code:
Sub ShowSelectedCellColorIndex()
Dim cell As Range
Set cell = Selection
If cell.Cells.Count > 1 Then
MsgBox "Please select only one cell.", vbExclamation
Exit Sub
End If
MsgBox "ColorIndex of the selected cell is: " & _
cell.Interior.ColorIndex, vbInformation
End Sub➤ Selection refers to the active cell; the macro exits if multiple cells are selected.
➤ Interior.ColorIndex retrieves the fill color’s index and shows it in a pop‑up.
➤ Return to Excel, click a colored cell (for example, a grade in Sheet1!C2:C11), press Alt + F8 , select ShowSelectedCellColorIndex, and run it.
➤ A message box instantly displays that cell’s ColorIndex, letting you identify colors without formulas or extra columns.
Use the ColorIndex Property to Read Cell Background Colors
Sometimes you need to capture the exact Color Index that’s already applied to cells, so you can reuse or document those colors in your code. VBA’s ColorIndex property lets you read that number in one line.
For this demonstration, our Students sheet has grades in column C already filled with colors (Green = 4, Yellow = 6, Orange = 46, Red = 3). The macro below will read each grade’s background color, write the Color Index in column D, and label the header so you can see every result beside the grades.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ListColorIndexes()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Sheets("Students") ' Change sheet name if needed
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
' Header for Color Index column
ws.Range("D1").Value = "ColorIndex"
' Loop through Grade cells and record ColorIndex in column D
For i = 2 To lastRow
ws.Cells(i, "D").Value = ws.Cells(i, "C").Interior.ColorIndex
Next i
MsgBox "Color indexes listed in column D."
End Sub 
➧ Interior.ColorIndex returns the background Color Index of each grade cell.
➧ The Color Index value is written to the matching row in column D.
➧ Adding a header in D1 keeps your sheet organized and clearly shows results.
➤ Return to Excel, press Alt + F8 , select ListColorIndexes, and click Run.
➤ Column D now shows the Color Index number for every grade, confirming the colors you applied earlier.
Identify Cell ColorIndex by Selecting a Cell
If you’re working with color-coded cells and want to find out which ColorIndex was used, you can create a macro that reveals the ColorIndex of any selected cell. This is especially useful when inspecting a dataset visually, like checking the color used for grades in your worksheet.
Instead of writing formulas or looping through ranges, this method simply shows a message box with the ColorIndex value when you select a cell and run the macro.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ShowSelectedCellColorIndex()
Dim cell As Range
Set cell = Selection
If cell.Cells.Count > 1 Then
MsgBox "Please select only one cell.", vbExclamation
Exit Sub
End If
MsgBox "ColorIndex of the selected cell is: " & cell.Interior.ColorIndex, vbInformation
End Sub
➧ If more than one cell is selected, the macro exits to avoid confusion.
➧ The macro uses Interior.ColorIndex to get the fill color's index and display it in a pop-up.
➧ This helps you quickly identify which color is applied to any cell in your dataset without modifying the worksheet.
➤ Return to Excel, click on a colored cell (like one of the grades in column C on Sheet1), and press Alt + F8 to run ShowSelectedCellColorIndex.
➤ A message box will appear showing the cell’s ColorIndex.
Retrieve Exact RGB Components with the Color Property
Sometimes you need the precise Red‑Green‑Blue makeup of a cell’s fill, not just its ColorIndex. VBA’s Color property returns a long integer representing the RGB value, which you can split into individual R, G, and B components for detailed reporting or custom formatting.
In this example, we’ll read each grade’s background color in Sheet1 column C, break the long integer into R, G, B parts, and list those values in columns E, F, and G.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste:
Sub ListRGBValues()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim clr As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' your sheet
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
' Headers
ws.Range("E1").Resize(1, 3).Value = Array("Red", "Green", "Blue")
For i = 2 To lastRow
clr = ws.Cells(i, "C").Interior.Color ' RGB as long
ws.Cells(i, "E").Value = clr Mod 256 ' Red
ws.Cells(i, "F").Value = (clr \ 256) Mod 256 ' Green
ws.Cells(i, "G").Value = clr \ 65536 ' Blue
Next i
MsgBox "RGB values listed in columns E‑G."
End Sub
➧ Using modulo and integer division extracts each color channel.
➧ Columns E, F, G will now show exact RGB components for every grade cell.
➤ Run ListRGBValues (Alt + F8) to see the RGB breakdown beside your data.
Capture ColorIndex with the DisplayFormat Property
Cells colored by conditional formatting don’t reveal their ColorIndex through the regular Interior.ColorIndex property. Instead, use DisplayFormat.Interior.ColorIndex to get the visible color after the conditional rules are applied.
This method scans scores in Sheet1 column B (assumed to have conditional formatting) and writes the resulting ColorIndex into column H.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste:
Sub ListCFColorIndexes()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' change sheet name if needed
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
ws.Range("D1").Value = "CF ColorIndex" ' header in column D
For i = 2 To lastRow
ws.Cells(i, "D").Value = ws.Cells(i, "B") _
.DisplayFormat.Interior.ColorIndex
Next i
MsgBox "Conditional‑format ColorIndexes listed in column D."
End Sub➧ DisplayFormat.Interior.ColorIndex, therefore, captures the effective ColorIndex even if no fill is directly set.
➧ Column H now lists the ColorIndex produced by your conditional‑format rules, handy for audits or further automation.
➤ Return to Excel, run ListCFColorIndexes, and verify the ColorIndex numbers match the colors produced by your conditional formatting rules.
Frequently Asked Questions
What is the difference between Color and ColorIndex in VBA?
The Color property returns an RGB color code as a long integer, providing precise color information. ColorIndex refers to a limited palette of colors indexed by number, which is simpler but less flexible.
Why does DisplayFormat.Interior.ColorIndex matter for conditional formatting?
Because conditional formatting colors are not stored directly in the cell’s Interior properties, using DisplayFormat lets you read the color that’s actually visible on the sheet after conditional rules apply.
Can I use these methods on charts or shapes?
No, these VBA properties work on worksheet cells only. Charts and shapes have their own color properties and require different methods to read or modify colors.
What happens if a cell has no fill color?
If a cell has no fill, ColorIndex usually returns xlNone (which is –4142), and Color returns 16777215 (white). Your code should handle these cases to avoid confusion.
Wrapping Up
Understanding how to find and work with color index numbers in Excel VBA helps you automate color-based tasks effectively. Whether you want to identify colors applied manually, by conditional formatting, or need exact RGB values, these methods cover all bases. By using the right property for your scenario, ColorIndex, Color, or DisplayFormat, you can write VBA code that reads and responds to cell colors reliably.
