Hi Ghauri,

Give this a look or adapt the code below to suit your sheet.

http://www.xldynamic.com/source/xld.ColourCounter.html


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

Sub SumColorCountYellow()
Dim Yellow6 As Integer
Dim Cell As Range

For Each Cell In Range("DataY")
If Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + Cell.Value
End If
Next

Range("F1").Value = "Yellow = " & Yellow6

MsgBox " Yellow adds to " & Yellow6, _
vbOKOnly, "CountColor"

Range("F1").Value = ""

End Sub

HTH
Regards,
Howard


"Ghauri" wrote in message
...
In my spread sheet I want to count the values of green, red and blue
background cells separetly, which are in a same row? i.e. I want excel to
only pick green cells and ignore others when counting the values. Does
anyone
used this type of fuction before? I was thinking to use sumbycolor
function,
but could not work it out.

Please
help.............................................. ..................