How to average the sum of cells depending on the colour?

[KurtisT6]

Hello i am looking to find a vba code. I want this code, for example in B13 to find the average of the sum home wins (B7 + B9). I tried to make a vba but I wasnt sure how to involve the average into it.

This was my VBA:

Function SumColor(MatchColor As Range, sumRange As Range) As Double

Dim cell As Range
Dim myColor As Long
myColor = MatchColor.Cells(1, 1).Interior.Color
For Each cell In sumRange
If cell.Interior.Color = myColor Then
SumColor = (SumColor + cell.Value)
End If


Next cell
End Function


ANY HELP WOULD BE APPRECIATED

 

[Zot]

Why not just count a number each time cell.Interior.Color = myColor, Once you get the final SumColor after completing the loop, just divide the SumColor with total count like

Function SumColor(MatchColor As Range, sumRange As Range) As Double

Dim cell As Range
Dim myColor As Long, n As Long
n = 0
myColor = MatchColor.Cells(1, 1).Interior.Color
For Each cell In sumRange
If cell.Interior.Color = myColor Then
SumColor = (SumColor + cell.Value)
n = n + 1
End If
SumColor = SumColor / n

Next cell
End Function

 

[KurtisT6]

hello, thanks for ur reply, when i use this code it comes up with "#value!"

 

[Zot]

hello, thanks for ur reply, when i use this code it comes up with "#value!"

So sorry. I put SumColor = SumColor / n inside your loop. Should be after all summed up

Here it is

Function SumColor(MatchColor As Range, sumRange As Range) As Double

Dim cell As Range
Dim myColor As Long, n As Long
n = 0
myColor = MatchColor.Interior.Color
For Each cell In sumRange
    If cell.Interior.Color = myColor Then
        SumColor = (SumColor + cell.Value)
        n = n + 1
    End If
Next cell
SumColor = SumColor / n

End Function

 

[KurtisT6]

legend thank you