add up a list of values depending on the color of the cell
 

Hello to you all,

Maybe a simple question, but I can't find the answer right now.

I have a list of values which have to be add up and give me a total.
Only I want to be able to leave out certain values, not by setting them to
zero but by giving the cell a different color.
(sheet already uses these colors to show something)

eg.
1
2
3
4
5

Total = 15

If cell with 2 is colored red, total should show 13.

How can this be done in macro's.
How do I get it to run after a change of color in the sheet. (macro should
run instandly)

Thanks in advance,

Kimball





(something like recalcute at opening or via a short-key)

add up a list of values depending on the color of the cell
 

See Chip Pearson's site for a SumByColor Function.

Note Chip's caveat about changing a color not triggering event code.

Also note CF colors caveat.

http://www.cpearson.com/excel/colors.htm


Gord Dibben MS Excel MVP

On Tue, 17 Oct 2006 12:11:44 +0200, "Kimball"
wrote:

Hello to you all,

Maybe a simple question, but I can't find the answer right now.

I have a list of values which have to be add up and give me a total.
Only I want to be able to leave out certain values, not by setting them to
zero but by giving the cell a different color.
(sheet already uses these colors to show something)

eg.
1
2
3
4
5

Total = 15

If cell with 2 is colored red, total should show 13.

How can this be done in macro's.
How do I get it to run after a change of color in the sheet. (macro should
run instandly)

Thanks in advance,

Kimball





(something like recalcute at opening or via a short-key)