I'm very new to VBA so excuse my ignorance and know that any help is much appreciated.
I am trying to create a function (in insert>module) to sum based on cell color and then separately (in the sheet1 code) use "Sheets(1).Calculate" to force recalculation of the function. What I thought was going to be a simple tutorial video of about 10 minutes has now turned into 6 hours of hell. The code seems to work at first, but after saving the sheet or making a change to any cell the code stops working and the cell using SumColor does not update unless I double click the cell and press enter.
Video I was following was "How to SUM Cells by Color in Excel (Get it to update automatically)" by Laila Gharani on YouTube.
First off, here's what I have:
(Insert>Module):
--------------------------------------
and in (Sheet 1>View Code):
Is there anything that would cause this to break upon saving the file (as .xlsm) or upon entering/deleting data from ANY cell in the sheet? As you can see in the code, i put some msgbox's in to try and diagnose where it breaks. The msgbox for "sheet" always pops up on selection change, but after saving/modifying cells the "module" msgbox fails to appear. At this point, i try going to Formulas Tab>Calculation>calculate sheet and this does not force recalculation of the cell calling the SumColor function. Should "calculate sheet" do this or is there another way to do it for functions?
Once my sheet is in this state the only way to make it work again is to go back to the module or sheet code windows and make literally any edit. Add a space, or press enter once or even press space then backspace and its all good until I hit save, etc...
I just cannot wrap my head around this. Please help.
Bookmarks