How to Return the Font Color From Excel Cell

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.

1. Open the Find and Replace window by pressing Ctrl + F on the keyboard.
2. Click on “Format”.
3. On the Font tab, select the color you want to search for.
4. Confirm with OK.
5. Click on “Find All”.
6. 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):

=ProfessorExcelFontColor(B5)

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.

=ProfessorExcelFontRGB(B5)

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

=PROFEXFontColor(B16,FALSE)

for the font index and

=PROFEXFontColor(B16,TRUE)

for the RGB value.

Or, you need a little bit more guidance? Insert the PROFEX function with the function tool:

1. Click on Function on the Professor Excel ribbon.
2. Select the function to insert (here: PROFEXFontColor).
3. As the cell reference, type or select the cell with the font color you want to return.
4. Type TRUE for the RGB value or FALSE for the color index.
5. 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.

Image by Gábor Adonyi from Pixabay