John,
The big problem lies in what is actually a feature of CF. If you select a
range of cells and apply CF, it will adjust any formula presented relative
to the position of the cell in the selection. This is a very convenient way
of selecting a range and using say
=AND(A1B1,C1TODAY())
next cell will adjust the row or column as appropriate. As I said, it is
very useful as it allows you to setup multiple cells at once, but the CF is
relative. And when you are not in that cell, you have to make an adjustment
for where the CF is being evaluated from. Therein lies the difficulty, and
thus the coding complexity.
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"John Coleman" wrote in message
ups.com...
Bob,
Impressive. I find it amazing how involved the code is. Conditional
formats ultimately involve a simple boolean value that the application
has stored *somewhere*, and given how important conditional formatting
is in many spreadsheets it is disappointing that this value is not
exposed in the object model. You are to be commended in being able to
discover a work-around. Thanks for the link!
-John Coleman
Bob Phillips wrote:
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.