excel 2003

S

st

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

Mike H

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
 
S

st

Steve said:
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.
Click to expand...
 
G

Gord Dibben

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 Edit>Find

Format>Format>Patterns. 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
 
Top