Re: Excel and Colours

Giganews Newsgroups
Subject: Re: Excel and Colours
Posted by:  L. Howard Kittle (lhkitt…@NOSPAMcomcast.net)
Date: Wed, 22 Oct 2003

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
"Dan" <anonymo…@discussions.microsoft.com> wrote in message
news:0b7701c39882$62179930$a10128…@phx.gbl...
> 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.

Replies

None

In response to

Excel and Colours posted by Dan on Wed, 22 Oct 2003