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 Object
    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.

alt To count the number of “red” words in column A we can simple to this:

COUNTIF(C2:C9,3)

“3” in the formula refers to the color red.

4 comentários:

Madelaine said...

Hi, i have a question do you know how can I combine 10 sheets into one, but I need keep the same format ex(the color on the font).

Madelaine said...

Hi, i have a question do you know how can I combine 10 sheets into one, but I need keep the same format ex(the color on the font).

Diane O said...

This function wouldn't work for me until I defined the GetFontColor as Long (not as Object) - and then it worked perfectly!

Anonymous said...

Thanks Diane O!! It wasn't working for me either until I made the change that you suggested.