Counting cells with colored font
 

Hi,I know this can be done with Excel 2003 onwards, but I've got 2002 and I
can't find a way to count the cells in a range which have blue font and which
ones have red font.
Any ideas?
Range is C5:E17
--
tia

Counting cells with colored font
 

This worked for me, just select a fixed cell and color code it so it knows
what color to refer to in the function. Add this macro to your visual basic..

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

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
--
Thanks!
Loretta


"JockW" wrote:

Hi,I know this can be done with Excel 2003 onwards, but I've got 2002 and I
can't find a way to count the cells in a range which have blue font and which
ones have red font.
Any ideas?
Range is C5:E17
--
tia

Counting cells with colored font
 

Hi Loretta, thanks for the reply. i'm not sure what you mean though!
How would the code know which cell to refer to, where would the result be
displayed.? Sorry to be a pain
Cheers
--
tia


"Loretta" wrote:

This worked for me, just select a fixed cell and color code it so it knows
what color to refer to in the function. Add this macro to your visual basic..

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

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
--
Thanks!
Loretta


"JockW" wrote:

Hi,I know this can be done with Excel 2003 onwards, but I've got 2002 and I
can't find a way to count the cells in a range which have blue font and which
ones have red font.
Any ideas?
Range is C5:E17
--
tia