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

John Coleman wrote:
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

wrote:
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


John Coleman wrote:
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

wrote:
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.