Sum cells by color

[jjbud44]

How do I add up all the cells in a column of a certain color?

Thanks

 

[John Waller]

How do I add up all the cells in a column of a certain color?

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

http://www.mrexcel.com/archive/VBA/18389.html

 

[Dave Peterson]

This isn't built into excel.

But if the cells are formatted because of Format|cells (not conditional
formatting), you can use the code at Chip Pearson's site:

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Zaidy036]

How do I add up all the cells in a column of a certain color?

Thanks

Try the free add in from ASAP-Utilities

 

[L. Howard Kittle]

Hi Sue,

John and Dave have given you some top notch sites to do what you are asking,
here is a garden version of adding the values in colored cells in a named
range Data. You will need to adjust the .ColorIndex number to suit the
particular color/s you have in your worksheet.

Does not work for conditional formatting of the cell color.

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:F12").Value = ""

End Sub

HTH
Regards,
Howard

 

[ozgrid.com]

Hi Sue

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm