Count of Font Format

[Steve]

Hi Claus I have tried the module approach as this would be ideal but
when I use it I get

#NAME#

Do you know how I can rectify this. The font color is definitely red
and not conditionally formatted.

Thanks

Steve



Hello Steve,

Am Thu, 07 Jun 2012 15:08:41 +0100 schrieb Steve:

I have text ithin cells which have been formatted with font colour red
and I would like to count the number of cells with this format. It is
not conditionally formatted but done manually.

Is it possible to count the number of cells with this font formatting?

You can filter for font color and then use SUBTOTAL
Your values in A1:A20
Filter for red color and then use
=SUBTOTAL(3,A1:A20)

Or use VBA to count your cells with red font color
Copy following code in a standard module
In the sheet use e.g. =ColorCount(A1:A20)

Function ColorCount(MyRange As Range) As Long
Dim rngC As Range

For Each rngC In MyRange
If rngC.Font.ColorIndex = 3 Then
ColorCount = ColorCount + 1
End If
Next
End Function


Please start a new thread for new questions.


Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

Am Thu, 07 Jun 2012 16:28:10 +0100 schrieb Steve:

but when I use it I get

#NAME#

try it with following code. In sheet you enter
=CountIfColor(A1:A20,3,1)
A1:A20 is your range, 3 is color index for red, 1 for font color = true
With
=CountIfColor(A1:A20,3)
you can count cells with red interior color:

Function CountIfColor(myRange As Range, _
myColor As Variant, _
Optional bolFont As Boolean = False) As Double

'Idee von Melanie Breden, © (e-mail address removed) / 13.10.2004 / 11.12.2004
'Funktion zur Anwendung von ZÄHLENWENN mit Hintergrundfarbe
'oder Schriftfarbe als Kriterium


Dim intColor As Integer
Dim rngCell As Range

If bolFont Then
If IsObject(myColor) Then
intColor = myColor(1).Font.ColorIndex
Else
intColor = myColor
End If

For Each rngCell In myRange
If rngCell.Font.ColorIndex = intColor Then
CountIfColor = CountIfColor + 1
End If
Next

Else
If IsObject(myColor) Then
intColor = myColor(1).Interior.ColorIndex
Else
intColor = myColor
End If

For Each rngCell In myRange
If rngCell.Interior.ColorIndex = intColor Then
CountIfColor = CountIfColor + 1
End If
Next

End If
End Function

Regards
Claus Busch