excel 2003
 

How do I count red dates in a column for a total. I have tried the "get font
color"formular, but it does not work. Do I need some kind of Add-in installed.

excel 2003
 

Hi,

you probably need a UDF. Alt +F11 to open VB editir. Right click 'This
workbook' and insert module. paste the code in on the right.

Call it with
=colourdates(A1:A22,3)

3 is Red. If your unsure of a colour number record a macro of yourself
setting a font colour and have a look at the macro. This code won't work if
the colour is a result of a conditional format, that's more complicated

Function ColourDates(Rng As Range, Colour As Integer) As Integer
Dim C As Range
For Each C In Rng
If IsDate(C) Then
If C.Font.ColorIndex = Colour Then
ColourDates = ColourDates + 1
End If
End If
Next
End Function

Mike

"st" wrote:

How do I count red dates in a column for a total. I have tried the "get font
color"formular, but it does not work. Do I need some kind of Add-in installed.

excel 2003 Tring to use a formula=getfontcolor((m3:m238),3))
 

"Steve" wrote:

How do I count red dates in a column for a total. I have tried the "get font
color"formular, but it does not work. Do I need some kind of Add-in installed.
I do not know VBA. But my results are #name. Is it a problem with the syntax?
Is there another formula. I am a complete novice.

excel 2003 Tring to use a formula=getfontcolor((m3:m238),3))
 

Unless you have a UDF named getfontcolor then you will have to make one.

See Chip Pearson's site for the cellcolorindex UDF

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

Good idea to download the module with all the functions and copy to your
workbook or Personal.xls

If you just want a count of red cells you can do it without VBA.

Select a range and EditFind

FormatFormatPatterns. Click on red pattern and OK

Find all.

In the "found" dialog box select top address then SHIFT + End to select all.

On Status bar right-click and "Count"


Gord Dibben MS Excel MVP

On Wed, 8 Oct 2008 09:13:07 -0700, st wrote:



"Steve" wrote:

How do I count red dates in a column for a total. I have tried the "get font
color"formular, but it does not work. Do I need some kind of Add-in installed.
I do not know VBA. But my results are #name. Is it a problem with the syntax?
Is there another formula. I am a complete novice.