A UDF for Counting Coloured Cells that are conditionally formatted

N

NeilG

Hi All,

I have been doing some extensive research and testing to determine whether
there is any way that a UDF can be created that will return the count for
cells on an input sheet that show a particular colour (as determined by CF).

So far it would appear that this is not possible. I have accessed some of
the most eminent MVPs and VBA gurus, but the conclusion would appear to be
(and borne out by my testing) "No - can't be done"

So this is my last attempt to find a solution - if it can't be done I can
laboriously go throgh my 50 odd input sheets and mirror the CF conditions in
a helper area and return a value of 1 each time the (complex) CF criteria is
met - count therefore now possible; however, I would much prefer a UDF if
possible.

On a more general note this requirement seems to crop up regularly with
developers, so I wonder if this is not something that MS should be addressing
directly?

Please find below the original question - and code blocks - that I put up.

Any help would be much appreciated - even it just confirms that this is not
possible.

Many thanks.

Neil

"Does anyone have the robust code for a UDF that can be used to count the
number of cells that conform to a certain (conditional) colour formatting (in
Excel 2007)
I found the following:

Function CountColor(Color As Range, Range As Range) As Long
Dim C As Range
For Each C In Range
If C.Interior.ColorIndex = Color.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function

but this does not work consistently, though, of the 3 I did download, (1
from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to
function, albeit in limited conditions.
I am using conditional formatting to draw the Users attention to invalid
entries and ideally want to create a "one stop shop" report which the User
can access easily to see if there are any invalid entries on any of the 50
plus input sheets i.e. without having to trawl through them individually.....
The Ozgrid code is:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As
Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'You can now use the custom function (ColorFunction) like;
'=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells
$A$1:$A$12
'that have the same fill color as cell $C$1. The reason it will SUM in this
example is because
'we have used TRUE as the last argument for the custom function.
'To COUNT these cells that have the same fill color as cell $C$1 you could
use:
'=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by
omitting the last argument
'our function will automatically default to using FALSE.
'Be aware that the changing of a cells fill color will not cause the Custom
Function to recalculate,
'even if you press F9 (Recalculates the whole Workbook). You will need to
either, select the cell
'and re-enter the formula, or go to Edit>Replace and replace = with =, or
use Ctrl+Alt+F9
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

and, whilst this seemed to work partially, it doesn't if the cell formats
change, which they will of course, and neither does it respond to F9
(re-calculate), as the author correctly points out.
Any guidance you can provide will be much appreciated.
Thank you."
 
D

Dave Peterson

Check Chip Pearson's site:
http://www.cpearson.com/Excel/CFColors.htm
But it's not for the faint of heart.

In fact, I'd use those mirror cells and then use them for the conditional
formatting rules for the real cells (no sense updating both sets of formulas
when something changes).

ps. I think I've seen where xl2010 is going to have a .displayformat (or
something like that) property that will make this easier.
 
G

gls858

NeilG said:
Hi All,

I have been doing some extensive research and testing to determine whether
there is any way that a UDF can be created that will return the count for
cells on an input sheet that show a particular colour (as determined by CF).

So far it would appear that this is not possible. I have accessed some of
the most eminent MVPs and VBA gurus, but the conclusion would appear to be
(and borne out by my testing) "No - can't be done"

So this is my last attempt to find a solution - if it can't be done I can
laboriously go throgh my 50 odd input sheets and mirror the CF conditions in
a helper area and return a value of 1 each time the (complex) CF criteria is
met - count therefore now possible; however, I would much prefer a UDF if
possible.

On a more general note this requirement seems to crop up regularly with
developers, so I wonder if this is not something that MS should be addressing
directly?

Please find below the original question - and code blocks - that I put up.

Any help would be much appreciated - even it just confirms that this is not
possible.

Many thanks.

Neil

"Does anyone have the robust code for a UDF that can be used to count the
number of cells that conform to a certain (conditional) colour formatting (in
Excel 2007)
I found the following:

Function CountColor(Color As Range, Range As Range) As Long
Dim C As Range
For Each C In Range
If C.Interior.ColorIndex = Color.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function

but this does not work consistently, though, of the 3 I did download, (1
from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to
function, albeit in limited conditions.
I am using conditional formatting to draw the Users attention to invalid
entries and ideally want to create a "one stop shop" report which the User
can access easily to see if there are any invalid entries on any of the 50
plus input sheets i.e. without having to trawl through them individually.....
The Ozgrid code is:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As
Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'You can now use the custom function (ColorFunction) like;
'=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells
$A$1:$A$12
'that have the same fill color as cell $C$1. The reason it will SUM in this
example is because
'we have used TRUE as the last argument for the custom function.
'To COUNT these cells that have the same fill color as cell $C$1 you could
use:
'=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by
omitting the last argument
'our function will automatically default to using FALSE.
'Be aware that the changing of a cells fill color will not cause the Custom
Function to recalculate,
'even if you press F9 (Recalculates the whole Workbook). You will need to
either, select the cell
'and re-enter the formula, or go to Edit>Replace and replace = with =, or
use Ctrl+Alt+F9
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

and, whilst this seemed to work partially, it doesn't if the cell formats
change, which they will of course, and neither does it respond to F9
(re-calculate), as the author correctly points out.
Any guidance you can provide will be much appreciated.
Thank you."

I use a program called ASAP Utilities. It's a freebie.
I find this little add on quite helpful.
http://www.asap-utilities.com/index.php

It contains quite a number of preprogrammed UDF's

While I've never used this function, in their online doc on page 122
they list the function below:

==ASAPCountShades(range) Not sure that's what you want but it's worth a
look.

http://www.asap-utilities.com/documentation-user-guide.php

gls858
 
L

Luke M

I agree, it would be nice if there was a simple way to check "current
displayed color" in a cell. As it is, it appears the closest workaround is to
determine which condition is currently active. Assuming the colors that you
are trying to count come from similar based CF's (the color red always comes
from condition 1?) you might check out this site:

http://www.cpearson.com/excel/CFColors.htm

I admit I didn't have time to test everything, but the intro paragraph
sounds like it provides the correct UDF's to accomplish your end goal.
 
J

JLatham

Actually, Chip's approach is the one I'd kind of figured on taking myself and
I even got started on it, but why reinvent the wheel? <g>. Rather than
trying to determine what conditional formatting has been applied by looking
at the cell's displayed shading/font settings, determine if any conditional
formatting has been applied at all. It makes for a slower operating
function, but one that is much more maintenance free than to try to detect
specific changes. You can quickly rule out the possiblity of any CF having
been applied by simply testing for a .FormatCondition(1).Type and if there
isn't one, then the cell doesn't have any (if it doesn't have 1, it can't
have more than 1).

I think Chip has even taken care of the extended number of conditional
formats that can be applied in Excel 2007 with
For Ndx = 1 To Rng.FormatConditions.Count
of course the side effect of that is that if the user has defined lots and
lots of conditional formats for lots and lots of cells, then the whole thing
takes lots and lots longer to arrive at the results. Tradeoffs, there are
always tradeoffs.
 
D

David McRitchie

Hi Neil,

If you just want to count the cells, you would probably
be better off using the same formulas that you used
for conditional formatting.

If you would like some help using Chip's formulas.

These may be of some help to you, but you will have
to get help elsewhere, and anything that is different in
Excel 2007 is not there. In particular look for Chip Pearson'
and John McGimpsey on these pages:

Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

and of course you found the formulas on Chip Pearson's page.
Color Functions In Excel
http://www.cpearson.com/excel/colors.aspx

In order to convert a complete spreadsheet into HTML everything
was included in macro in

Excel to HTML conversions
http://www.mvps.org/dmcritchie/excel/xl2html.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top