counting red cells

[chris2727272727]

i have a large range of data but some of the cells are red i want a cell at the bottom olf the data to calculate the number of red cells in the sheet.

 

[chicagodiceman]

Welcome To The board

I'm sure that one of the true Gurus on this board will be able to come up with a more eloquent way of doing this but...

If your data isn't too large, you could use a macro to store a count of cells formatted with red text something like this. It assumes your data begins in a1 and there are no blank cells in your data range:

Dim Count_Red As Integer
Sub findred()
Count_Red = 0
Range("a1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Font.Color = vbRed Then
Count_Red = Count_Red + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = Count_Red
End Sub

 

[apolloh]

Macro4's Solution
http://www.ozgrid.com/forum/showthread.php?t=16850

 

[Richie(UK)]

Hi Chris,

Chip Pearson has some useful routines for working with colour. Have a look here.

HTH

 

[chicagodiceman]

And now I've learned something else. This board is great. Thanks Richie