Try this with modifications to do green. Where it sums the values of
Orange, Red and Green cells, (seperately) in the named range Data
Option Explicit
Sub SumColorCount()
Dim Orange46 As Integer, _
Red3 As Integer, _
Green4 As Integer
Dim Cell As Range
For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 46 Then
Orange46 = Orange46 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + Cell.Value
End If
Next
Range("F10").Value = "Orange = " & Orange46
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Green = " & Green4
MsgBox " You have: " & vbCr _
& vbCr & " Orange " & Orange46 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4, _
vbOKOnly, "CountColor"
Range("F10").Value = ""
Range("F11").Value = ""
Range("F12").Value = ""
End Sub
Or this may be all you need, which sums the value of green cells in the
named range DataY.
Neither will work if the cell color if from Conditional Formatting.
Sub SumColorCountGreen()
Dim Green4 As Integer
Dim Cell As Range
For Each Cell In Range("DataY")
If Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + Cell.Value
End If
Next
Range("F1").Value = "Green = " & Green4
MsgBox " Green adds to " & Green4 , _
vbOKOnly, "CountColor"
Range("F1").Value = ""
End Sub
Dave McRitchie has a site with his professional touch to do this. Danged if
I can find it now, however... thought I had it logged in my Bookmarks.
HTH
Regards,
Howard
