VBA is stopping me from being able to undo

By default, all VBA action cannot be undone. You just have to save it before you run the code and if you want to cancel the result, load without saving.

VBA clears any "Undo" cache as well. So you will need some code to reverse the operation.

Read link for more detail (and sample way to do it in code).
http://spreadsheetpage.com/index.php...ba_subroutine/

And below link for way to write class module to handle this.
http://www.jkp-ads.com/Articles/UndoWithVBA00.asp

Okay so I have attached the excel spread sheet, I have just taken that section of a spread sheet and edited all the data as it was confidential and I have adjusted formulas so it should all work now.

So I am using the VBA to count the number of coloured cells in column T so I can work out an appraisal percentage.

It is not the Function procedure that is clearing the Undo stack, it is the selectionchange event procedure that clears the undo stack.

You can create an "on undo" procedure in which you can put whatever code you need to "undo" the actions of the selectionchange procedure, but you still cannot preserve the undo stack before that point. https://msdn.microsoft.com/VBA/Excel...o-method-excel

If being able to undo is critically important to what you are doing in Excel, it might be wisest to figure out how to use that function procedure in a cell rather than calling it from a selection change event.

Hello FraserMc97,

So is there no way I can stop it automatically updating with every cell click?

Apart from what MrShorty stated, the answer is no, there is no way you can stop it automatically updating with every cell click.

Regards.

So is there no way I can stop it automatically updating with every cell click?

What exactly are you trying to do here?

To stop cells T3 and U3 from updating with each cell click, delete the "selection change" event procedure (this is what "selection change" means -- every time you click a different cell, the selection changes, and the selection change event is triggered).

I could see putting =colorfunction(T2,T9:T158) into T3 then copy over to U3. As the function is written, these cells would only update when you enter the cell, press F2 and enter (since cell color will not always trigger these cells to calculate since cell color change is not something that fits into the calculation dependency tree). You could add Application.volatile to the UDF, then it would calculate with each calculate event (color changes will not usually trigger a calculate event, though F9 triggers a calculate event). Using a VBA UDF inside of a cell like this does not effect the undo stack.

Those are just some possible ideas. In the end, this is a case where I think you need to think through exactly what you are trying to do with these cells and this spreadsheet so you can figure out a good way to achieve these goals.

If it helps, I would agree with CK76 -- cell color is not a good "datum" to use in a spreadsheet. Perhaps the best approach to this is to come up with a different datum type to base these cells on. Again, this probably means going back to the beginning and thinking through exactly what this sheet is supposed to do.

I am not in a position to debug for you right now. As I explain in this post (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I would do would be to add a stop statement to the beginning of the function to enter debug mode. A #VALUE! error like that usually indicates that there is some critical syntax error in the function call (wrong number of arguments or incorrect data types or something) or some syntax error in the function that is causing it to terminate early. If you add the Stop statement and it fails to enter debug mode, then something is wrong with the function call. If it enters debug mode, then you can step through the function to find the statement that is causing it to terminate early (and maybe get a more meaningful error message).