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

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).

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).

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).

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


"Phil H" <(E-Mail Removed)> wrote in message
news:02975D2F-56E6-4376-8755-(E-Mail Removed)...
> 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).

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

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

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



"Phil H" <(E-Mail Removed)> wrote in message
news:02975D2F-56E6-4376-8755-(E-Mail Removed)...
> 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).

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.


"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).

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 Chip’s 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