How to determine and identify the background color index of cells in Excel?
When you receive a sheet with multiple colorful cells as below screenshot shown, in some cases, you may want to identify the background color index of these color cells. There is no built-in feature that can determine the color index of a cell, but, in this article, I introduce some VBA codes to solve this job in Excel quickly.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Do as following steps to determine the color of the cell by VBA.
|Kutools for Excel, with more than 120 handy functions, makes your jobs easier.|
1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Application window.
2. Click Insert > Module to open a new Module and paste below VBA code to the blank script. See screenshot:
VBA: Get traditional hex code of cell
Function getRGB1(FCell As Range) As String 'UpdatebyExtendoffice20170714 Dim xColor As String xColor = CStr(FCell.Interior.Color) xColor = Right("000000" & Hex(xColor), 6) getRGB1 = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2) End Function
3. Save the code and close the VBA window. Select a blank cell next to the colored cell, type this formula, =getRGB1(A16), then drag the autofill handle over the cells you want to use. See screenshot:
Tip: there are some other codes can identify the color index of cell.
1. VBA: Decimal value for each code
Function getRGB2(FCell As Range) As String 'UpdatebyExtendoffice20170714 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 getRGB2 = "R=" & R & ", G=" & G & ", B=" & B End Function
2. VBA: Decimal values
Function getRGB3(FCell As Range, Optional Opt As Integer = 0) As Long 'UpdatebyExtendoffice20170714 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 getRGB3 = R Case 2 getRGB3 = G Case 3 getRGB3 = B Case Else getRGB3 = xColor End Select End Function
Select Duplicate & Unique Cells (quickly select duplicate or unique values in a column.
- How to change font color based on cell value in Excel?
- How to color duplicate values or duplicate rows in Excel?