Count text with different color in a range

[Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,
I have 200+ columns of text data, very simple data like "AA" or "CC", some of which is in red font, rest in black font. I need to count the cells in each column with red font. Thanks!


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think you have to create a custom function that would go thru each cell and then give you count based on font color
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Use this User defined Function:

here ColorCell is the cell with the required font color and CountRange is the range for which you want to count the mention font color cell.
Function ColorCount(colorCell As Range, countRange As Range)   
Dim rCell As Range   
Dim cellCol As Integer   
Dim ans As Long   
ans = 0   
cellCol = colorCell.Interior.ColorIndex   
For Each rCell In countRange   
If rCell.Interior.ColorIndex = cellCol Then   
ans = ans + 1   
End If   
Next rCell   

ColorCount = ans   

End Function