Excel and Colours

[Dan]

Hello, could anyone out there please help me???

I have a progress reporting document i have set up in
Excel. Each cell along a row has a value (0.5,1,1.5,etc.)
at the end of the row is the cumulative value of these
cells.

The medium of reporting is to colour a cell when the
activity is completed.

Is there any way that a macro can be written so that when
a cell is coloured in by using the FILL COLOUR command on
the toolbar,that the value of that particular cell is
added to the value in the cumulative cell at the end of
the row?

All advice will be greatly appreciated.

Thank You.

 

[Andy B]

Dan

Try Chip's site:

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

Andy

 

[L. Howard Kittle]

Hi Dan,

Chip's site is proffesional, here's an amateurs shot at it. You will need
to tweak the code to suit your exact needs on the sheet. This sums the
values of cells colored orange, red or green in a range named Data.

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