count if font and background color condition is true

I

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
 
I

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,
 
T

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
 
I

Ivano

Hi Tom,
Thanks. It works and has helped

Tom Hutchins said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top