How do you count colored cells?

[Clueless78217]

I am working on a spreadsheet that counts my wins and losses regarding NFL
predictions. (i.e. marking the team name in red font which represents my
picks and yellow fill with the correct winner; the counting is needed to
tally up my wins and losses involving my predictions - teams with red font
and yellow fill = wins, teams with black font and yellow fill = losses)

I would like an answer that would allow me to use a function/formula
actually in Excel, not programming of some sort.

TIA

 

[Pete_UK]

You can't count based on formatting using a standard function - you
will need some VBA to give you that functionality, unless your colours
are based on conditional formatting (which they don't seem to be from
your description).

Pete

 

[Shane Devenshire]

Hi,

Here is a custom function to count cells base on font color and fill color:

Function CountFormats(R As Range, E As Range) As Integer
Dim cell As Range
Dim Total As Integer
Application.Volatile
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
With cell
If .Interior.ColorIndex = S.Interior.ColorIndex _
And .Font.ColorIndex = S.Font.ColorIndex Then
Total = Total + 1
End If
End With
Next cell
CountFormats = Total
End Function

In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the
range you want to check and D2 is a cell formatted to the desired format.

To add this code to a workbook press Alt+F11 and select your file in the
Project explorer in the top left side of the screen. Choose Insert, Module.
Put the code in the resulting module.

If this helps, please click the Yes button

Cheers,
Shane Devenshire