Bob,

Your advice is probably the way to go, but do you have any idea why the
following doesn't work as intended?

Function ColorSum(R As Range, i As Long) As Variant
Dim sum As Variant
Dim cl As Range
For Each cl In R.Cells
If cl.Interior.ColorIndex = i Then
sum = sum + cl.Value
End If
Next cl
ColorSum = sum
End Function

Sub RedGreenSums()
Dim R As Range
Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp))
Range("B1").Value = ColorSum(R, 3)
Range("C1").Value = ColorSum(R, 10)
End Sub

Private Sub Worksheet_Calculate()
RedGreenSums
End Sub

'For debugging purposes:

Sub ShowIndex()
On Error Resume Next
MsgBox Selection.Interior.ColorIndex
End Sub


It works fine if the colors are manually set. But - if they are set by
conditional formatting then wierd things happen. I set up a trial sheet
in which the first 10 cells of column A were colored green for positive
values and red for negative values. When I actually change the values
from positive to negative or vice versa I get (when I run the ShowIndex
sub on various cells) that all colorindices are -4142. Maybe that is
some alias for xlAutomatic or something like that and the conditional
formatting in effect doesn't change the color per se but changes the
meaning of automatic for that cell. But then - this raises the
question: how can you determine the color of a cell if its color has
been set by conditional formatting? I guess you would have to determine
programmatically which condition applies and go inside the
corresponding formatcondition object - which seems like a lot of effort
to read what should be an easy property to read off.

-John Coleman

Bob Phillips wrote:
SUM them based upon the same condition that is applied to CF.

For instance, if the CF is greater than 10, then use

=SUMIF(A1:A10,"10")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi,

I am not sure Excel 2003 can do this or not. Here is what I would like
to do.
I have a column (say column A) with some numbers. Some of the cells are
colored green and some are colored red.
I would like to add all the cells colored in red at cell B1 and add all
the cells colored in green at cell C1. The color may be changed
dynamically by means of conditional formatting.

Any idea?

Thanks.