John,
They do, that is what the CF colour is. It is difficult, but not impossible
to get the CF colour. See
http://xldynamic.com/source/xld.CFConditions.html
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"John Coleman" wrote in message
oups.com...
A few seconds after I posted I realized that it is obvious that
conditional formatting shouldn't change the colorindex - this allows
the original colorindex to stick around as a default color when none of
the conditions apply. It would be nice if cells had a
DisplayedColorIndex property in addition to a ColorIndex one. I'll have
to write my own function. No need to answer my previous post.
John Coleman wrote:
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.