Average by color VBA
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
|