Recognize Cell Color and Sum Data accordingly

[Universal Pegasus - Chad]

I've tried SUMIFS to recognize cell colors and add the numbers (which are
hours worked in the day) in the corresponding row. The kicker is the yellow
cell doesn't necessarily have the same hours and I can't get Excel to
recognize colors only for one of my criteria. It could range from 2 to 12
hours for any given day. The row may contain data with up to five different
colors associated depending on the kind of work performed that day. So let's
say I want to pull all yellow cells for a given time period and it looks
something like the following.

A B C D
E F
Yellow Cell Blue Cell Yellow Cell Red
Cell Black Cell
1 1/1 1/2 1/3
1/4 1/5
2 4-Man Crew 10 10 8
8 6

Indications below column headers in example above are not in spreadsheet.
Could not color B2 Yellow, C2 Blue, D2 Yellow for these purposes. Thanks

 

[Jim Thomlinson]

Take a look at this link...

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

 

[Fred Smith]

Excel supports colors for visual purposes. Trying to get it to do
calculations on colors will lead to nothing but frustration. You're better
off adding cells which identify the type of work done. Then Sumifs is easy.

Regards,
Fred

"Universal Pegasus - Chad" <Universal Pegasus -
(e-mail address removed)> wrote in message
news:[email protected]...

 

[L. Howard Kittle]

Try this for the yellow cells which may get you started. You will need to
name the range from which you want to add the yellow cells values to DataY.
Select the cells on the sheet and in the name box enter DataY then Enter.

Copy this code and paste in the VB editor and give it a go. Sums the value
in each yellow cell in DataY and displays in a message box and on the
worksheet.

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


You might want to check this out if you, say have Orange, Red and Green, as
an example. Name the range to test this Data. Sums the value in each
color in Data and displays in a message box and on the worksheet.

Both will need to tweeked to suit your final worksheet and you should test
these on a test worksheet and neither will work if the cells are colored by
conditional formatting.

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

"Universal Pegasus - Chad" <Universal Pegasus -
(e-mail address removed)> wrote in message