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

Use the filter function to filter or sort by font color.
Use the filter function to filter or sort by font color.

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

Use the Find function to search and select by font color.
Use the Find function to search and select 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
Return the color index with a short VBA macro.

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
Return the RGB value of the font color.
Return the RGB value of the font color.

In your Excel cell, use the following function. Again, please replace the B5 with your cell reference.

=ProfessorExcelFontRGB(B5)


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Method 3: Comfortably return the text color with Professor Excel Tools

Use Professor Excel Tools for returning the font color. For free!
Use Professor Excel Tools for returning the font color. For free!

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:

Return the font color for free with Professor Excel Tools.
Return the font color for free with Professor Excel Tools.
  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.


Professor Excel Tools Box

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.


Image by Gábor Adonyi from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *