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.
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.
5 comentários:
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).
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).
This function wouldn't work for me until I defined the GetFontColor as Long (not as Object) - and then it worked perfectly!
Thanks Diane O!! It wasn't working for me either until I made the change that you suggested.
Thanks for this - likewise, changed object to long and worked a treat (Excel 2013)
Post a Comment