Will excel add cells using colour coding eg Add all red cells

[Guest]

I have colour coded a spreadsheet and would like to set a cell to recognise
the couloured cells and add all these cells to a total

 

[cparaske]

Not sure if you can actually add colored cells.

What is the content inside the cell? If there is a pattern, you can
try a few things to get a sum.

 

[Peo Sjoblom]

Nothing built in

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

will do it

 

[L. Howard Kittle]

Peo recommends Chip Pearson's site to get this done, and of course he is a
pro.
Here is an armature shot at it that sums the values in the orange, red and
green cell. Will not work if the color is a result of conditional
formatting. Not sure if Chip's will overcome the conditional formatting,
been awhile since I looked at it.

Where Data is a named range on the worksheet.

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

HTH
Regards,
Howard

 

[Chip Pearson]

Not sure if Chip's will overcome the conditional formatting,

been awhile since I looked at it.

The code on my colors page does not take into account colors of
Conditional Formatting, only normal formats.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com