Add cells based on background colour - Glitch

Hi All,

I recently created a macro (with a little help from some forums) that was designed to sum a row of cells IF the cell formatting used a particular colour in the background. It worked a treat, and this is what it looked like

Please Login or Register  to view this content.

It does what I want it to do, except when I use 'undo'. For instance, I change one of the cells to my special background colour (blue) - the sum changes as planned, but then if I click undo, the background changes back to the old colour (grey) but instead of the sum changing again, it keeps the new total. i.e the sum of blue cells and one grey cell. I want it to only show me the totals of my blue cells.

Can anyone suggest how I ought to tweak the above code so that it will always show the correct total?

Post withdrawn

you will find the issue with UDF is it doesnt react to changes made to your spreadsheet without "re-running" the code
i assume you know you can update by going into the cell with the formula press f2 and enter which is why you want something more "dynamic"

Please Login or Register  to view this content.

adding in application.volatile will somewhat remedy this but be warned, it does degrade performance due to the constant running of the code..this becomes important if the size of your spreadsheet is large or the number of times you use this UDF

if its only something small or you dont use this UDF often you probably wont notice

ps even with this, you need to force update (F9) in order to update formula

not a problem
glad to help

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

try using double instead of long