P
Phil H
Need a formula to count the cells in a range with a background color yellow
(6).
(6).
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
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
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
L
Luke M
Correction, change this line:
Application.Volatile = True
to just
Application.Volatile
Application.Volatile = True
to just
Application.Volatile
P
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.
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.
J
JLGWhiz
If your color was set by conditional format, then Luke's function will not
work.
work.
D
Dave Peterson
Try:
=personal.xls!countyellow(a2:a6935)
=personal.xls!countyellow(a2:a6935)
Phil said: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.
J
JLGWhiz
Chip Pearson has a lot of info on counting colors at this site:
http://www.cpearson.com/excel/colors.aspx
http://www.cpearson.com/excel/colors.aspx
P
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.
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.
D
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.
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.