On Aug 30, 1:13 pm, "Bob Phillips" wrote:
Wouldn't the blanks not be coloured?

Function AVGColor(rColor As Range, rng As Range)
Dim iCol As Integer
Dim C As Range
Dim vResult As Double
Dim Count As Long

iCol = rColor.Interior.ColorIndex
For Each C In rng
If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) Then
vResult = vResult + C.Value
Count = Count + 1
End If
Next rCell

If Count = 0 Then
AVGColor = ""
Else
AVGColor = vResult / Count
End If

End Function

--
HTH

Bob

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

"jlclyde" wrote in message

oups.com...



On Aug 30, 12:49 pm, "Bob Phillips" wrote:
should be

Function AVGColor(rColor As Range, rng As Range)
Dim iCol As Integer
Dim C As Range
Dim vResult As Double
Dim Count As Long

iCol = rColor.Interior.ColorIndex
For Each C In rng
If C.Interior.ColorIndex = iCol Then
vResult = vResult + C.Value
Count = Count + 1
End If
Next rCell

If Count = 0 Then
AVGColor = ""
Else
AVGColor = vResult / Count
End If

End Function

--
HTH

Bob
The formula needs to skip blank cells liek the Average formula in
Excel. This Function does what mine does, just more efficiently.
Jay- Hide quoted text -

- Show quoted text -

This is what the code shoudl be. Thank you for turing me onto the
right answer.
Jay
If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) And Not
IsEmpty(C.Value) Then