I would like to count cells in excel based on the color of the text in the cell. I found the code below. It seems to work well, except in regard to one thing: when I have a cell within the range that is formatted to utilize the specific color being identified, the code will count that cell even if the cell has nothing in it. Is there any way to modify this formula such that the count will only include cells within the range that have data in it (or, more specifically if it matters, a number in it)?
Thanks!!
Code:
------------------------------------------------------------
Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
'Update 20140210
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
CountColour = CountColour + 1
End If
Next
End Function
-----------------------------------------------------------
This is the corresponding code I am using for sum, by the way, which seems to work well:
Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
'Update 20140210
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
xTotal = xTotal + rng.Value
End If
Next
SumByColor = xTotal
End Function
Bookmarks