I am trying to count the number of cells in a range that have
..Font.ColorIndex=6
AND
..Interior.ColorIndex=[1, 2, 3 or 4]

I've tried using the UDFs from http://www.cpearson.com/excel/colors.htm but
I'm having no luck in combining these with a formula.

Does anyone know how to do this?

Kind regards,
Samuel Harmer

Function CountByColor(InRange As Range, _
FontColorIndex As Integer, _
InteriorColorIndexMin As Integer, _
InteriorColorIndexMax As Integer) As Long

Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If Rng.Font.ColorIndex = FontColorIndex And _
Rng.Interior.ColorIndex = InteriorColorIndexMin And _
Rng.Interior.ColorIndex <= InteriorColorIndexMax _
Then CountByColor = CountByColor + 1
Next Rng

End Function

Hth,
Merjet

How about:

Sub styne()
IAmTheCount = 0
Set r = Selection
For Each rr In r
i = rr.Font.ColorIndex
j = rr.Interior.ColorIndex
If i = 6 And j < 5 Then
IAmTheCount = IAmTheCount + 1
End If
Next
MsgBox (IAmTheCount)
End Sub
--
Gary's Student
gsnu200704


"styne666" wrote:

I am trying to count the number of cells in a range that have
.Font.ColorIndex=6
AND
.Interior.ColorIndex=[1, 2, 3 or 4]

I've tried using the UDFs from http://www.cpearson.com/excel/colors.htm but
I'm having no luck in combining these with a formula.

Does anyone know how to do this?

Kind regards,
Samuel Harmer