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