how to use sumbycolor fuction?

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................................................................

Ghauri

Assuming you copied the sumbycolor code from Chip Pearson's site and placed it
in a general module of your workbook.............

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

Assuming your range of cells is A1:X1

In A4 enter =sumbycolor(A1:X1,number)

Where number is the index number of the the background color.

If you don't know the index number, go back to Chip's site and copy the code
for the CellColorIndex Function.

Place it in same module.

In A2 enter =cellcolorindex(A1) drag/copy across to X2

You will get a number for each color to use in your sumbycolor function.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 09:50:24 -0800, Ghauri <[email protected]>
wrote:

>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................................................................

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" <[email protected]> wrote in message
news:[email protected]...
> 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................................................................