Check excel cell if font is a certain color?

I want to use vba to check if the text in a cell in excel is of a certain color, lets say red. Any ideas?

1 Answer

I want to use VBA to check if the text in a cell in excel is of a certain color

lets say red

You can use Font.ColorIndex

See below for an example.

To retrieve the cell colour use:

=GetFontColor(A1)

VBA – Get font color Function

When we have colors on our sheets data and want, for instance, to count how many “red” words we have on our sheet, that is not possible because there is no formula in Excel to check for font colors. Instead we can create our own VBA Function to get the font color. It’s a very simple code. You have to insert it on a VBA module on your sheet.

Function GetFontColor(ByVal Target As Range) As Integer GetFontColor = Target.Font.ColorIndex
End Function

Then you can use it on your sheet like this:

=GetFontColor(A2)

Below is an example on how you can use this function. In column C we put the font color of text in column A.

To count the number of “red” words in column A we can use:

COUNTIF(C2:C9,3)

The 3 in the formula refers to the color red.

Source VBA – Get font color Function. Script has been tweaked to match the requirements of the question.

What if I want the cell colour?

Use the following function:

Function GetCellColor(ByVal Target As Range) As Integer GetCellColor = Target.Interior.ColorIndex
End Function

To retrieve the cell colour use:

=GetCellColor(A1)

Source Sum Cells based on Background Color. Script has been tweaked to match the requirements of the question.

Further Reading

• Color Palette and the 56 Excel ColorIndex Colors