I am currently working on an excel spreadsheet called Calendar.xlsm I have Excel2010.
My workbook has the following worksheets:
Cover Sheet
Calendar
Colour Detection
Printable
The "Colour Detection" sheet uses a User Defined Function to detect the colours of cells on the "Calendar" worksheet and based on that colour returns a number for that particular cell; For example if Calendar!C3 has a fill colour of "light green" then Colour Detection!C3 = 43
Just for completeness the UDF is called ColorIndex and was written by Bob Phillips.
I then use conditional formating to colour cells in my "Printable" worksheet based on the number stored in "Colour Detection".
My problems start when there is a colour change on the "Calendar" sheet, usually when this happens "Colour Detection" remains unchanged unless I select the cells manually and press return.
My knowledge of how Excel works with Visual Basic in this area is very limited but growing.
My first idea for a solution was to record a macro where I selected each cell and hit return one by one. I then attached the macro to a button called Calculate on the "Cover Sheet". Immediately this didn't work because there are approximately 800 cells that use this UDF.
While trying this I thought I stumbled on the solution, I noticed that if I applied my solution to only a few cells, the whole worksheet seemed to update. I then applied this and tested it a few times and it worked. I then presented my work to a friend as the masterpiece I was sure it was, and while demonstrating it to them it all fell over and only the cells I selected in my macro updated.
Next I tried adding the line
Application.Volatile
to my UDF but this doesn't to make a difference.
My question is how do I easily force every single cell in my worksheet to update by doing something simple like pushing a recalculate button?
Try Ctrl-Alt-F9 this forces every formula in the workbook to be calculated. Or you could set workbook.forcefullcalculation to True in your workbook_Open event.