Sub Macro4()
Dim myRange As Range
Dim cell As Range
Dim redCounter As Long

redCounter = 0:

Set myRange = Range("A1:A7")
For Each cell In myRange
If cell.Interior.ColorIndex = 3 Then
redCounter = redCounter + 1
End If
Next cell
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Khurram" wrote in message
oups.com...
Hi all,
I'm given a single column of data that covers a variable number of
rows. I need to count the number of rows that contain text that is
coloured red. It sounds simple and I've been trying to use the
Format.Find function but that never comes to an end. Is there a way
for me to go through the range of cells and and count how many are
coloured red? There no other colours except black and red. Below is
my attempt at this but does not work.

Sub Macro4()
Dim myRange As Range
Dim cellValue As String
Dim redCounter As Long

redCounter = 0:

Set myRange = Range("A1:A7")
myRange.Select

Do Until (myRange.End(xlDown) = True)

With Application.FindFormat.Font
.Subscript = False
.ColorIndex = 3
End With
Selection.Find(What:="", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=True).Cells.Select
redCounter = redCounter + 1:
Selection.FindNext(After:=ActiveCell).Activate
Loop

End Sub

Thank you kindly
Khurram