Sum based on any color background 1

[Mich2too]

I am being asked to keep track of multiple checking accounts by creating a daily bank reconcilaiton using Excel 2007. As checks, deposits, transfer, etc show as being posted online banking, those items are marked as cleared in Excel by changing the cell background to a color. Each day, a different color is used to mark the cleared items. Then I have to recalculate the colulmns to deduct those items which have cleared, which can lead to human error the more items.

I am NOT familiar with VBA and am having difficulties using the info that is being found on the web in order to make this work for me.

What I want to do is have the columns automatically resum themselves as a colored background is added.

Thank you in adanvce for any help.

 

[Lilliabeth]

Your problem should be simple but it is complicated by the use of formatting instead of data as the indicator.

You should have a column for DateCleared. It's a pretty simple thing to click in a cell and type Ctrl+; , or even a few characters.
Do that and then you can easily use Sumif().

But since that isn't what you want to do, you can filter your list and use a subtotal function, but I am guessing you want to see the formula results without having to apply a filter.

The best thing to do is to insist this bad practice of using formatting to indicate a date be stopped.

Otherwise, you are looking at using code. A lot of trouble just to save typing a few characters for a date.

--Lilliabeth

 

[SkipVought]

Hi,

Lilliabeth has given your EXCELLENT PROFESSIONAL advice!

You do NOT want to color code instead of having a REAL DATE; believe you me!!!

That would be amateur, a royal disaster, and incredibly stupid!!!

Every transaction should have a real date and a column for cleared.

You ought to be able to use the SUMIFS function.

Post back for further help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mich2too]

Thank you both for the quick responses. Didn't expect anything during a weekend.

Adding the a cleared column sounds like the best idea yet. ANYTHING is better than continuing to allow for human error that comes from re-selecting every colored cell to total up the 'cleared' items. And since I'm the human who can make the errors, will make me feel much better and more confident in the outcome!!!

THANKS AGAIN!!