Let’s assume the following situation: You have received an Excel file and someone has highlighted different cells. Now you want to read out the different background color codes in order to convert the file into a proper Excel data table. Unfortunately, there is no direct built-in way to solve this. So, let’s see how to solve this.
- Example for returning the background color
- Solution 1: Use a workaround to achieve your goal
- Solution 2: Use a simple VBA code to retrieve the background color code
- Solution 3: Comfortably use an Excel add-in to return the background color
Example for returning the background color
The example in this article: Someone has highlighted cells in column D. We want to retrieve the background color code of these cells in column E.
Solution 1: Use a workaround to achieve your goal
Unfortunately, Excel does not provide any built-in method to retrieve the color of the cell background. That – as usually – means that you have to use a VBA macro. But let’s take a step back. Do you really need the background color code retrieved by a function? Yes, there might be reasons, for example when you want to automate or frequently update the file. But if that’s not the case, why don’t we look for a workaround?
One workaround could be, to add a filter. Filters offer to a way to work with background colors:
- Select the whole table.
- Click on the Filter icon on the Data ribbon in order to insert a filter.
- Use the filter: Click on the small filter icon of the column you want to filter. This is the column with the colorful background (here: column D).
- In the poped-up filter menu, click hover with the mouse over “Filter by Color”.
- Click on one of the colors you are interested in retrieving the color code from (here: yellow). Now, only rows with this color are visible.
- Select the cell range to insert the background color codes to. Type “Yellow” (or whatever keyword / code you prefer). Instead of pressing just Enter on the keyboard, hold down the Ctrl-key and press Enter. That way, the text you have typed is inserted in all selected cells.
Solution 2: Use a simple VBA code to retrieve the background color code
The second method is to use a VBA macro. It can be very simple: Actually just three lines of code could solve this.
But first, let’s think about what type of data you want to return. When it comes to colors, the most common types are RGB (stands for red, green and blue) and is actually three values. In Excel, however, “long” color codes are typically easiest to use because they are built-in with VBA functions. And then there are hex codes (they typically start with the #-sign).
Return the long color code of the cell background color
Let’s explore how we return the “long” background color codes first.
First step: Open the VBA editor, insert a new VBA module and copy & paste the following code (here are the steps in detail).
Function ProfessorExcelBackgroundColor(cell As Range) ProfessorExcelBackgroundColor = cell.Interior.Color End Function
After that, use the function name “ProfessorExcelBackgroundColor” in your Excel sheet and link to your desired cell. It should look something like this:
Your return value is the long value. You could already start to work with these values, for example by inserting a small lookup table which translates the few color codes to actual names (such as yellow for code 65535 like in the example).
Return the RGB color code with a VBA macro
Returning the RGB code of the background color requires a few more lines of code. Basically, it works like before: You copy and paste the following VBA macro into a new module (again, here is help if needed).
Function ProfessorExcelBackgroundRGB(cell As Range) Dim PROFEXColorIndex As Long Dim PROFEXColor As Variant PROFEXColorIndex = cell.Interior.Color PROFEXColor = PROFEXColorIndex Mod 256 PROFEXColor = PROFEXColor & ", " PROFEXColor = PROFEXColor & (PROFEXColorIndex \ 256) Mod 256 PROFEXColor = PROFEXColor & ", " PROFEXColor = PROFEXColor & (PROFEXColorIndex \ 256 \ 256) Mod 256 ProfessorExcelBackgroundRGB = PROFEXColor End Function
Next, you use the function =ProfessorExcelBackgroundRGB(A1) to return the background RGB code from cell A1.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Solution 3: Comfortably use an Excel add-in to return the background color
You want an easier method? Yes, there is one! Our Excel add-in “Professor Excel Tools” can do all that.
Simply click on the insert function button on the Professor Excel ribbon. Then, select the background color function and fill out the fields:
- Select the cell in which you want to insert the function to return the background color.
- Click on “Function” on the Professor Excel ribbon.
- Next, select “PROFEXBackgroundColor”.
- As the cell, select the source cell which you want to return the color code from (here: cell A6).
- You can further specify, if you want to return the RGB code (write TRUE) or the long code (write long).
- In the preview section below, you can see the entire function as well as the result. Just click on Insert Function to insert it into your selected cell.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Please feel free to download all examples from above in this comprehensive Excel workbook. Just click on this link and the download starts right away.