Sum cells by color

  • Thread starter Thread starter jjbud44
  • Start date Start date
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.)
 
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
 
Back
Top