CountIF by Background Color
 

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

CountIF by Background Color
 

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

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.

"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

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


"Phil H" wrote in message
...
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.

"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

Try:

=personal.xls!countyellow(a2:a6935)



Phil H wrote:

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.

"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

--

Dave Peterson

CountIF by Background Color
 

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

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



"Phil H" wrote in message
...
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.

"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

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 Chips approach, with his code, and got the #Name! error.


"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

CountIF by Background Color
 

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.

Phil H wrote:

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 Chips approach, with his code, and got the #Name! error.

"Luke M" wrote:

Correction, change this line:

Application.Volatile = True

to just

Application.Volatile

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

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

--

Dave Peterson