IF Formula

[Cathie G]

I have several cells with specific colors. Is there a way to count these
cells?

 

[Bernard Liengme]

Chip has stuff on this at www.cpearson.com
best wishes

 

[Shane Devenshire]

Hi,

Here is a fancy version of code to count cells that have various formats
such as color

Function CountFormats(R As Range, E As Range) As Integer
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

You would enter the custom function in the spreadsheet
=CountFormats(A1:F13,K1)

And the function would count the number of cells in the range A1:F13 that
match the format of K1 for the formmating options in the code. You can take
out IF .Font statements and leave just the Interior.Color one.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire