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 day
and she's decided to just change the color of the hours that day from
black to red. Another day might be blue, or yellow... each color
determined by which ever company was being billed.

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

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

Thanks for any help with this!!
Jason


--
jkwok
------------------------------------------------------------------------
jkwok's Profile: http://www.excelforum.com/member.php...o&userid=28428
View this thread: http://www.excelforum.com/showthread...hreadid=492771

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jkwok" wrote in
message ...

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 day
and she's decided to just change the color of the hours that day from
black to red. Another day might be blue, or yellow... each color
determined by which ever company was being billed.

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

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

Thanks for any help with this!!
Jason


--
jkwok
------------------------------------------------------------------------
jkwok's Profile:
http://www.excelforum.com/member.php...o&userid=28428
View this thread: http://www.excelforum.com/showthread...hreadid=492771

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

"jkwok" wrote in
message ...

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 day
and she's decided to just change the color of the hours that day from
black to red. Another day might be blue, or yellow... each color
determined by which ever company was being billed.

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

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

Thanks for any help with this!!
Jason


--
jkwok
------------------------------------------------------------------------
jkwok's Profile:
http://www.excelforum.com/member.php...o&userid=28428
View this thread: http://www.excelforum.com/showthread...hreadid=492771