Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel 2000/2003 Formula Question

Aug 7, 2004 7:14AM PDT

I wonder if anyone knows of a way to sub-total a column of numbers based on the background color of the cell.

I keep a basic Excel 2003 worksheet for the family budget. Column titles for the months, row titles for the bills. When a bill amount is paid, I color the background of that cell yellow so I can tell at a glance what's been paid and what hasn't for a particular month.

For instance, right now there are several numbers in the August column with yellow backgrounds and the rest of the amounts still have no background color.

Is there a way to keep a running total of the items left to be paid each month? In other words, can you add a column of numbers and exclude the ones with a background color?

What I've been doing so far is using a simple formula of "=cell+cell+cell+cell+cell...", but you have to go into the formula and delete cells manually as items are paid. I'm looking for an easier way . . .

Hope this hasn't been too wordy. Thanks!

Discussion is locked

- Collapse -
Re: Excel 2000/2003 Formula Question
Aug 9, 2004 2:34AM PDT

It's not possible exactly as you want it, so I suggest a slightly different user interface, combined with a not-so-difficult macro.

Say the amounts are in column C. Then add a column D where you make a 'status' field right to the amounts. Contents could be, for example, O for Open, and P for paid.

Then use function dsum for the calculation. It add's only those rows from a database where some other field (like the status) has a certain value.

Then make a macro that sets the background color of the amount depending on the status of the row. It could be an worksheet change macro, testing if status-fields are changed (intersect is the thing to use). If so, loop through all statusses changing the colour of the cell left to it depending on the value of the cell.

I think it won't be more than 15 lines of code, and if you buy a good book on Excel macro's and spend some 15 hours studying it, you'll be able to write it yourself. After that investment, of course, you'll write other macro's much and much faster.

Hope this helps.


Kees

- Collapse -
Re: Excel 2000/2003 Formula Question
Aug 10, 2004 4:07AM PDT
- Collapse -
An alternative.
Aug 11, 2004 5:33AM PDT

Although your module should work, I don't particularly like:
- the hard-coded range
- the hard-coded color
- the button needed to activate it

Let me offer an alternative?

Define a function (you can define your own functions in Excel) like color_sum (ref_cell, range). It should compare the color of all cells in the range to the color in the reference cell, and if they are equal adds its value to the function value. I'm not sure if this functions is activated when you change the color of a cell in the range, so you might have to press f9 to do a full recalc.

Kees

- Collapse -
Re: An alternative.
Aug 11, 2004 6:43AM PDT

Before I posted the code I tried it out. It worked as expected - it added only the red-colored cells. You may not like it but it works. You simply have to open the worksheet you want it implemented drag a button from the Forms toolbar onto the worksheet, a window will open with the name Assign Macro click the New button, paste this code and save. Try it and you'll see.