count if font and background color condition is true

[Ivano]

Hi,
I want to count the number of cells that have the font color in -4105
(autocolor) and a background color of -4142 (no fill). I can't figure out
how to make the code work to count when both conditions are met.

I have found the following code:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

 

[Ivano]

I should also mention that I need my spreadsheet to display all three counts,

1. how many font in autocolor (which is the If statement)
2. how many background in no fill (which is the Else statement)
3. how many have both autocolor and no fill (which what I need and can't
figure out)

Thanks,

 

[Tom Hutchins]

You'll need a different function to return the count of cells meeting both
conditions.

Public Function CountByColor2(InRange As Range, _
FontColorIndex As Integer, _
FillColorIndex As Integer) As Long
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
If (Rng.Font.ColorIndex = FontColorIndex) And _
(Rng.Interior.ColorIndex = FillColorIndex) Then
BothCnt = BothCnt + 1
CountByColor2 = CountByColor2 + 1
End If
Next Rng
End Function

To call it:
=CountByColor2(A1:B11,-4105,-4142)

Hope this helps,

Hutch

 

[Ivano]

Hi Tom,
Thanks. It works and has helped

You'll need a different function to return the count of cells meeting both
conditions.

Public Function CountByColor2(InRange As Range, _
FontColorIndex As Integer, _
FillColorIndex As Integer) As Long
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
If (Rng.Font.ColorIndex = FontColorIndex) And _
(Rng.Interior.ColorIndex = FillColorIndex) Then
BothCnt = BothCnt + 1
CountByColor2 = CountByColor2 + 1
End If
Next Rng
End Function

To call it:
=CountByColor2(A1:B11,-4105,-4142)

Hope this helps,

Hutch