How to automatically sum based upon interior fill color when the fill color changes

Hi,
I have a budget spreadsheet. Each month, users are able to change the interior fill (Green = ok, Yellow = monitor, Red = bad). The spreadsheet has multiple column (B10:E79) and totals on row 80 for each column. On row 82 in each column I wish to have a total for all the green filled cells, row 83 for the yellow filled cells, and row 84 for the red filled cells. Each month, when the interior fill changes, the totals in rows 82, 83, and 84 need to be automatically updated.

I know Excel does not consider changing a cell's color to be significant to the calculation; thus, won't recalculate a formula when a cell color is changed. Simply changing a cell color does not cause a calculation, so the function is not recalculated, even with Application.Volatile True. Knowing this, how can I get the totals in rows 82, 83, and 84 need to be automatically updated each time the fill color changes? Assume the Color Index is green=4, yellow = 6, red = 3) if that helps.

Suggestions? Thanks for your help!

Right now, the user can manually change the cell color using the "Fill Color" option, but I'm open to suggestions on ways. Ideally, the user would be restricted to the three colors I mentioned in my original post so as to avoid various shades of red, for example, on the same sheet as some people may interpret a light shade as being "less bad" than a darker shade of red when in reality "red is red"! Example, if the user enters $100 in cell B20, the user has the option of only choosing green, yellow, red, or blank (no color).

Thanks a bunch Leith! This works pretty well, but it is not quite right for three reasons in order to resolve the overriding problem I am encountering in item #3 below:

1) If the user enters the dollar value and then selects a color from the dropdown before pressing the enter key, then a run-time error ‘1004’ “Method ‘Undo’ of object ‘_Application’ failed.

2) After entering a dollar value, the user has to press “enter” twice for the cursor to move down to the next cell. If the user forgets to press “enter” twice, it will lead to data entry errors.

3) In the example you provided, I need row 13 to total the green values in that column, row 14 to total the yellow values in that column, and row 15 to total the red values in that column whenever the dollar value changes or the background color changes.

Hello Leith,
Thanks for persevering and attaching the file. We’re just about there! The “run-time error 1004” still occurs, though.

If the user puts a dollar value in Column A, presses ENTER and then goes back to that cell to select a color from the dropdown, everything works fine.

If the user selects a color from the dropdown then puts a dollar value in Column A and presses ENTER, everything works fine.

If the user puts a dollar value in Column A and then immediately selects a color from the dropdown before pressing the ENTER key, I get a “run-time error ‘1004’ “Method ‘Undo’ of object ‘_Application’ failed”. The totals in rows 13:15 are updated just before the error message occurs, though.

Also, if the user accidently puts anything other than numeric values in Column A, presses ENTER and then goes back to that cell to select a color from the dropdown, a “Run-time error ‘13’: Type mismatch” error occurs. I can’t put in a second data validation in those cells for data entry because of the dropdown list already in use.

By the way, your SumByColor() solution is pretty slick. Thanks!

Hello Leith,
I appreciate the time you're taking to assist me. I've attached my workbook per your request.

If you put your cursor anywhere in A1:A10, enter any dollar value and then before pressing ENTER, select a color from the dropdown...you'll get the “run-time error 1004” message.

If you put your cursor anywhere in A1:A10, enter a non-numeric item, press ENTER, then go back to that cell to select a color from the dropdown...you'll get the "Run-time error ‘13’: Type mismatch" message.

Much thanks!

Hi Leith,
I tried sending you a private message, but it doesn't look like it happened (nothing in my "sent" box).

I'm wondering if you've had an opportunity to check further into the issues we were working on a couple weeks ago. I'm starting holidays on Feb 15 and would like to have these issues resolved for my end users before I leave. I recognize your expertise is in high demand, so if you don't have the time to graciously help me any further, I'll understand. Please let me know so I can plan for my next steps. Thanks! Murray