You have received an Excel file and somebody has highlighted text with a different font color? If you are talking about a few cells only – no problem. But what, if you file has hundreds of rows? How do you return the fore color from the text (or number) cells?
Method 1: Do it manually with these tricks
Unfortunately, Excel does not provide any built-in method to retrieve the font color of the cell. That – as usually – means that you have to use a VBA macro. But let’s take a step back. Do you really need the font 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?
Use the filter by color function
The filter in Excel is very powerful. Besides normal filtering, it can also sort data or filter and sort by color. If the items in your list or table have different colors (also background colors), the “Filter by Color” option is not greyed-out.
So, just filter here for your desired color and then you can add color labels (for example “red” in the column next to the filtered column).
Use the Find function to select cells by font color
If you want to search for specific colors, you can use the Excel Find window to search for a format.
- Open the Find and Replace window by pressing Ctrl + F on the keyboard.
- Click on “Format”.
- On the Font tab, select the color you want to search for.
- Confirm with OK.
- Click on “Find All”.
- Select all the search results. Excel then selects automatically the respective cells in your workbook.
Method 2: Return the font color with a short VBA macro
Return the index of the fore color
If you just need the color index, you can use the following very simple VBA macro. Open the VBA editor (Alt + F11 on the keyboard), insert a new module and paste the following code. Please refer to this article for all the steps in detail.
Function ProfessorExcelFontColor(cell As Range) ProfessorExcelFontColor = cell.Font.Color End Function
Back in your worksheet, type the following function (replace B5 with your desire cell reference):
Return the RGB code for the font color
Similar to the previous VBA macro (above), you can insert a slightly modified version. It returns the RGB code of the font color.
Again, open the VBA editor window (press Alt + F11 on the keyboard), insert a new module and paste the following source code.
Function ProfessorExcelFontRGB(cell As Range) Dim PROFEXColorIndex As Long Dim PROFEXColor As Variant PROFEXColorIndex = cell.Font.Color PROFEXColor = PROFEXColorIndex Mod 256 PROFEXColor = PROFEXColor & ", " PROFEXColor = PROFEXColor & (PROFEXColorIndex \ 256) Mod 256 PROFEXColor = PROFEXColor & ", " PROFEXColor = PROFEXColor & (PROFEXColorIndex \ 256 \ 256) Mod 256 ProfessorExcelFontRGB = PROFEXColor End Function
In your Excel cell, use the following function. Again, please replace the B5 with your cell reference.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Method 3: Comfortably return the text color with Professor Excel Tools
This is probably the most convenient method: Use our Excel add-in “Professor Excel Tools”. Install the add-in and type
for the font index and
for the RGB value.
Or, you need a little bit more guidance? Insert the PROFEX function with the function tool:
- Click on Function on the Professor Excel ribbon.
- Select the function to insert (here: PROFEXFontColor).
- As the cell reference, type or select the cell with the font color you want to return.
- Type TRUE for the RGB value or FALSE for the color index.
- Click on “Insert Function”.
Easy, right? And the best: Although the free trial version expires after 7 days, this function you can continue to use. It’s free. The reason is that if you collaborate with someone else the =PROFEX() functions should work for all users – even if they don’t have the unlimited license.
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.