if statements with colors?

[jkwok]

Hi,

My boss has a spreadsheet for employee hours for the whole month
Each day varies for who needs to be billed for the work done that da
and she's decided to just change the color of the hours that day fro
black to red. Another day might be blue, or yellow... each colo
determined by which ever company was being billed.

The problem she has brought to me is she'd like to create a summar
of the hours worked for each company and to have it summed up based o
the color used that day.

So all the red days would be added together, all the blues, all th
yellows... etc, etc. I'm almost certain this isn't possible, as
assume the colors are purely cosmetic and for easier readability. I
there any other way to do this? Like assign a variable or companyID t
each day... where from that I could create a generic if statement t
sum up the hours worked?

Thanks for any help with this!!
Jaso

 

[Bernard Liengme]

Chip has something on counting by colour at www.cpearson.com
best wishes

 

[L. Howard Kittle]

Chip's site will have all you need to do that and more, but just for fun,
here's my stab at it. "Data" is a named range. Will not work if color is
result of 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