Average numbers by fill color
 

I still need an answer to Average cells that have the same fill
color. But only use the cells that have numbers in them. Some may
contain words. They are mostly blank. Here is my code so far.

Thanks,
Jay
Function AVGColor(rColor As Range, Range As Range)
Dim rCell
Dim iCol As Integer
Dim C As Range
Dim vResult As Double

iCol = rColor.Interior.ColorIndex
For Each rCell In Range
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

For Each C In Range

If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then
Count = Count + 1

End If
Next C

If AVGColor = vResult / Count = Numeric Then
AVGColor = vResult / Count
Else: AVGColor = ""
End If

End Function

Average numbers by fill color
 

I answered that 2 hours ago in the other thread.

--
---
HTH

Bob

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



"jlclyde" wrote in message
oups.com...
I still need an answer to Average cells that have the same fill
color. But only use the cells that have numbers in them. Some may
contain words. They are mostly blank. Here is my code so far.

Thanks,
Jay
Function AVGColor(rColor As Range, Range As Range)
Dim rCell
Dim iCol As Integer
Dim C As Range
Dim vResult As Double

iCol = rColor.Interior.ColorIndex
For Each rCell In Range
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

For Each C In Range

If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then
Count = Count + 1

End If
Next C

If AVGColor = vResult / Count = Numeric Then
AVGColor = vResult / Count
Else: AVGColor = ""
End If

End Function

Average numbers by fill color
 

On Aug 30, 3:56 pm, "Bob Phillips" wrote:
I answered that 2 hours ago in the other thread.

--
---
HTH

Bob

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

"jlclyde" wrote in message

oups.com...



I still need an answer to Average cells that have the same fill
color. But only use the cells that have numbers in them. Some may
contain words. They are mostly blank. Here is my code so far.

Thanks,
Jay
Function AVGColor(rColor As Range, Range As Range)
Dim rCell
Dim iCol As Integer
Dim C As Range
Dim vResult As Double

iCol = rColor.Interior.ColorIndex
For Each rCell In Range
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

For Each C In Range

If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then
Count = Count + 1

End If
Next C

If AVGColor = vResult / Count = Numeric Then
AVGColor = vResult / Count
Else: AVGColor = ""
End If

End Function- Hide quoted text -

- Show quoted text -

THe answer that you gave put out the same results that i was getting.
Your code counted blank cells as did mine. I want to skip cells that
do nto ahve numbers in them. For example I have 6 cells with color
and 3 of them have the number one in them. the average shoudl be 1
and it says .50.
Jay

Average numbers by fill color
 

When you are in a thread always post to the same thread. Don't start new
threads with the same question

http://www.cpearson.com/excel/newposte.htm


--
Regards,

Peo Sjoblom


THe answer that you gave put out the same results that i was getting.
Your code counted blank cells as did mine. I want to skip cells that
do nto ahve numbers in them. For example I have 6 cells with color
and 3 of them have the number one in them. the average shoudl be 1
and it says .50.
Jay

Average numbers by fill color
 

No, I responded to that comment, which you would have seen if you stuck to
the same thread.

--
---
HTH

Bob

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



"jlclyde" wrote in message
ps.com...
On Aug 30, 3:56 pm, "Bob Phillips" wrote:
I answered that 2 hours ago in the other thread.

--
---
HTH

Bob

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

"jlclyde" wrote in message

oups.com...



I still need an answer to Average cells that have the same fill
color. But only use the cells that have numbers in them. Some may
contain words. They are mostly blank. Here is my code so far.

Thanks,
Jay
Function AVGColor(rColor As Range, Range As Range)
Dim rCell
Dim iCol As Integer
Dim C As Range
Dim vResult As Double

iCol = rColor.Interior.ColorIndex
For Each rCell In Range
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

For Each C In Range

If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then
Count = Count + 1

End If
Next C

If AVGColor = vResult / Count = Numeric Then
AVGColor = vResult / Count
Else: AVGColor = ""
End If

End Function- Hide quoted text -

- Show quoted text -

THe answer that you gave put out the same results that i was getting.
Your code counted blank cells as did mine. I want to skip cells that
do nto ahve numbers in them. For example I have 6 cells with color
and 3 of them have the number one in them. the average shoudl be 1
and it says .50.
Jay

Average numbers by fill color
 

On Aug 30, 6:04 pm, "Bob Phillips" wrote:
No, I responded to that comment, which you would have seen if you stuck to
the same thread.

--
---
HTH

Bob

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

"jlclyde" wrote in message

ps.com...



On Aug 30, 3:56 pm, "Bob Phillips" wrote:
I answered that 2 hours ago in the other thread.

--
---
HTH

Bob

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

"jlclyde" wrote in message

groups.com...

I still need an answer to Average cells that have the same fill
color. But only use the cells that have numbers in them. Some may
contain words. They are mostly blank. Here is my code so far.

Thanks,
Jay
Function AVGColor(rColor As Range, Range As Range)
Dim rCell
Dim iCol As Integer
Dim C As Range
Dim vResult As Double

iCol = rColor.Interior.ColorIndex
For Each rCell In Range
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

For Each C In Range

If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then
Count = Count + 1

End If
Next C

If AVGColor = vResult / Count = Numeric Then
AVGColor = vResult / Count
Else: AVGColor = ""
End If

End Function- Hide quoted text -

- Show quoted text -

THe answer that you gave put out the same results that i was getting.
Your code counted blank cells as did mine. I want to skip cells that
do nto ahve numbers in them. For example I have 6 cells with color
and 3 of them have the number one in them. the average shoudl be 1
and it says .50.
Jay- Hide quoted text -

- Show quoted text -

Bob,
This is what the code shoudl read. Thank you for pointing me in the
right direction.
Jay
If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) And Not
IsEmpty(C.Value) Then