Conditional Computing

[aung]

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.

 

[John Coleman]

Hi,

You can do this in VBA of course (hint: use the worksheet_calculate
event). But - before you go that rout, have you considered using the
conditional sum wizard? It is designed to create conditional sum
formulas using the same sorts of criteria as are used in conditional
formatting. From your problem description, this seems like a good
possibility. It is an add in. Go to tools -> add ins and install it if
it isn't already. This may be better than a VBA approach since, for
example, the code might break if you decide you want to use different
colors in the conditional formatting. It would probably also be quicker
if you are talking about a large number of cells.

HTH

-John Coleman

 

[Bob Phillips]

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)

 

[NickH]

I think John's probably right, especially if you are already using
Excel's built-in Conditional Formatting.

However, should you decide to go the VBA route the following functions
may be of some help.

NB. These functions will NOT detect a cell interior colour that has
been set using conditional formatting. To do that you would need to
write code to interrogate the condtional format settings, in which case
you might as well write your own conditional formatting routine (yeah,
listen to John).

Still reading? Okay go here for some Conditional Format macro
suggestions...

http://www.mvps.org/dmcritchie/excel/condfmt.htm

Here's a function to do the summing...

Public Function ColorSum(mRng As Range, mColor As Integer) As Single
Dim mTot As Single
Dim c As Range


For Each c In mRng
If IsNumeric(c.Value) Then
If c.Interior.ColorIndex = mColor Then
mTot = mTot + c.Value
End If
End If
Next c

ColorSum = mTot

End Function

To use enter something like =ColorSum(A1:A20,10) into a cell.

And here's a function to return the interior colour index of a cell...

Public Function GetColorIndex(mCell As Range) As Integer
''' Quick check to find the interior color of a cell. _
If multiple cells selected only top left examined

GetColorIndex = mCell.Range("A1").Interior.ColorIndex
End Function

To use enter something like =GetColorIndex(A3) into a cell.


Hope you listened to John - I'm just killing time. ?;^)

NickH

 

[John Coleman]

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

 

[John Coleman]

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.

 

[Bob Phillips]

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]

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]

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(A1>B1,C1>TODAY())

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)

 

[NickH]

Very useful stuff Bob, Thanks.

NickH

 

[aung]

Hi Guys,

Thank you so much for all your help. Very impressive indeed.

Aung

 

[aung]

Hi John,

I tried using Conditional Sum suggested by you and its purpose is not
the way I wanted.
In fact, here is what I am trying to achieve.
Let's say I have a list of expenses (10 items) in column A.
3 items have been paid and therefore I will color them in green.
4 items are due to pay in 2 days time and therefore I will color them
in red.
3 other items can be paid later.
What I would like to do is based on the color; I would like to compute
total amount I have paid, total amount I need to pay in 2 days and
remaining total amount.

Any good idea?

Thank you!

Aung

 

[Peter Huang [MSFT]]

Hi

I think we may try to use the VBA to sum the values.

1. the text of the value is Red.

Sub SumFontRed()
Dim rg As Range
Dim i As Integer
For i = 2 To 7
Dim sum As Integer
Set rg = Cells(i, 1)
If rg.Font.ColorIndex = 3 Then
sum = sum + rg.Value2
End If
Next
MsgBox sum
End Sub

2. the backgroud of the cell is in Red.
Sub SumBackgroudRed()
Dim rg As Range
Dim i As Integer
For i = 2 To 7
Dim sum As Integer
Set rg = Cells(i, 1)
If rg.Interior.ColorIndex = 3 Then
sum = sum + rg.Value2
End If
Next
MsgBox sum
End Sub

NOTE: here the ColorIndex = 3 means it is red.

To get the value conveniently, we may try to use the Record Macro function
to record a macro to see when we set the text to red, what is the macro
code recorded.
If you have any concern on this issue, pleaes feel free to let me know.
Thanks!



Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bob Phillips]

Peter,

The OP mentioned that the colours would change dynamically with conditional
formatting. That code you show will not get this, it gets the cell/text
colour. See other posts in this thread to see how that needs to be
addressed.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

John,

Reverting back on one of your statements about a simple Boolean stored
somewhere, whilst we might think that the object model should contain such a
property, I don't believe that it does. CF is effectively volatile, that is
it is evaluated every recalculation causes CF to be re-evaluated. It is this
that makes me think that Excel does the same sort of determination that my
code does. I guess it is a trade-off, re-calculate all of the CF conditions
every time the range/sheet/book is recalculated, or every time a dependent
or precedent cell involved in the CF is changed. Whilst I think it is the
former, it would certainly be preferable IMO if it were the latter, our
functions would then recalculate when the CF caused a state change, which we
cannot do now.

Regards

Bob

 

[John Coleman]

Aung,

Are all of the colors under the control of conditional formatting? If
so - the way to go is to concentrate on the conditions rather than the
colors. If neither SUMIF not the conditional sum wizard is sufficienlty
flexible (though this seems unlikely from your brief description), then
a VBA function that loops through the cells testing the relevant
condition is the way to go.

If none of the colors are under the control of conditional formatting,
then there again is a pretty easy solution based on looping through the
cells and checking colorindices. 3 posters, including myself in my
second post, have given variations of the same code for this.

It is when there is a mixture of the two cases that things would start
to get dicey. It might require the somewhat involved strategy of
downloading Bob's code to analyze conditional formatting and
integrating it with the colorindex approach. Or - it *might* have a
local-tech approach. If you start by manually coloring cells and then
putting conditions on top of (some?) of those colors then perhaps the
manual colors can be thought of as providing a baseline sum which can
be computed via the colorindex approach and then the conditional part
can be thought of as providing a correction term which is computed via
SUMIF and then added or subtracted to the baseline. This seems overly
complex to me - if you have a case of a mixture of manual and
conditional colors, see if you can make *all* of the color explicitly
conditional by adding a default condition (which shouldn't be much of a
problem unless you hit the three condition limit ) then try to use a
SUMIF approach.

So - just what role does conditional formatting play in your situation?

-John Coleman

 

[John Coleman]

Nick,

This post taught me something I never realized. I'm so used to thinking
of user-defined-functions as needing to be pure functions - limited to
returning a value which can be displayed in a cell and with no
side-effects involving formatting, etc. - that I had implicitly assumed
that UDFs couldn't even involve formatting. Hence my somewhat
round-about suggestion of linking my version of the colorsum function
to the calculate event. But when you wrote "To use enter something like
=ColorSum(A1:A20,10) into a cell." I realized that the only
restriction was on the "output" of the UDF but that the "input" can
involve virtually anything. I'm not quite sure where I would use that
fact, but I'm sure that sooner or later it would come in handy. Thanks.

-John

 

[Peter Huang [MSFT]]

Hi Bob,

I think this can be adpated by add a paramater in the macro.
e.g.
Sub SumFontRed(ByVal color As Integer)
.....
rg.Font.ColorIndex = color
.....
End Sub


Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[aung]

John,

Thank you so much for all your post.
I can ignore conditional formatting for this moment and work with VBA
code provided earlier.

Thanks!
Aung

 

[Bob Phillips]

Hi Peter,

I don't see how that suddenly addresses CF colour, it may be more flexible
but it is still cell colour.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)