In Excel, how do I "Countif" a cell is a certain format?

  • Thread starter Thread starter Pheasants21
  • Start date Start date
P

Pheasants21

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?
 
Hi,

You can add the following code to your workbook

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex <> S.Font.ColorIndex Then T = False
If .Interior.ColorIndex <> S.Interior.ColorIndex Then T = False
If .Font.Bold <> S.Font.Bold Then T = False
If .Font.Italic <> S.Font.Italic Then T = False
If .Font.Underline <> S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

In the spreadsheet you can enter the function as follows:

=CountFormats(A1:F13,H9)

This function will count the number of cells in the range A1:F13 that have
the same formatting as cell H9. This function check five types of
formatting, you can remove any of those from the code. Font Color, Interior
Color, Bold, Italic, Underline. As written all these formats must match, so
if you only want to deal with fill color it would read

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Interior.ColorIndex <> S.Interior.ColorIndex Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function
 
Back
Top