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.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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?