CountIF by Background Color

[Phil H]

Need a formula to count the cells in a range with a background color yellow
(6).

 

[Luke M]

You can use this short UDF called CountYellow. Note that it only counts true
background color, not format generated by conditional formatting. Detecting
conditional formatting is quite a bit more complicated...

Public Function CountYellow(r As Range) As Double
Application.Volatile = True

CountColor = 0
For Each c In r
If c.Interior.ColorIndex = 6 Then
CountColor = CountColor + 1
End If
Next

End Function

 

[Luke M]

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

 

[Phil H]

Luke,

I put this (corrected) function code in a new module 3 in personal.xls and
used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name?
error in the cell.

 

[JLGWhiz]

If your color was set by conditional format, then Luke's function will not
work.

 

[Dave Peterson]

Try:

=personal.xls!countyellow(a2:a6935)

Luke,

I put this (corrected) function code in a new module 3 in personal.xls and
used the following cell formula: =COUNTYELLOW(A2:A6935), and get a #Name?
error in the cell.

 

[JLGWhiz]

Chip Pearson has a lot of info on counting colors at this site:

http://www.cpearson.com/excel/colors.aspx

 

[Phil H]

All,

Color is not set by conditional formatting.

Used =personal.xls!countyellow(a2:a6935) and got a compile error – variable
not defined on the line CountColor =0, and #Value! Error in the cell.

Tried Chip’s approach, with his code, and got the #Name! error.

 

[Dave Peterson]

There is an option that you're using that forces you to declare all your
variables. CountColor is not defined.

Try this:

Option Explicit
Public Function CountYellow(r As Range) As Double
Application.Volatile True
Dim CountColor As Long
Dim c As Range

CountColor = 0
For Each c In r
If c.Interior.ColorIndex = 6 Then
CountColor = CountColor + 1
End If
Next c

'add this line, too
CountYellow = countcolor

End Function

As for the error you got from Chip's code...

You didn't put it in a general module--or you made some other typing error in
the formula. Are you sure you spelled the function name correctly (or included
the "personal.xls!" characters???)

You should give a little more info when things don't work.