Undo/Redo not working after running VBA

[kowolo]

I have a spreadsheet that runs a VBA code for conditional formatting. The code works fine, but now undo/redo/repeat, etc. are disabled for the entire workbook (even the cells that are not in the range for the conditional formatting).

All my other Excel files are fine so this is not a program-wide issue. Just this file is affected.

Is there anyway I can reenable these functions?

Kolette

 

[shg]

Once a macro makes a change to the environment (changes a cell value, a format, almost anything), the Undo stack is flushed. It's a good reason to be circumspect about adding VBA (other than UDFs) to a worbook.

 

[kowolo]

Is there a better way to add multiple conditions? I need six different colors for various cells so I tweaked a code I copied from a website. I'm not a programmer. What is a UDF? How could I use that in this instance?

 

[shg]

A UDF is a user-defined function. It's not relevant to your issue because UDFs are specifically prevented from doing anything except returning a value to the cell in which they appear (just like native Excel functions). I mentioned them only because they are examples of VBA that doesn't kill Undo.

I expect you have this code running in response to a change event. Change it so you run it only as needed, e.g., from a button. It will still kill Undo, but only when you run it.

Or apply the conditional formatting statically beforehand. If you're using Excel 2007+, you can have as many conditions as you like.

 

[kowolo]

I am running in response to a change event. I need the colors to update as I enter the information so pressing a command button, especially since it will turn everything off as soon as it runs, is probably not going to be a great solution. Thanks for your help though. I really appreciate your time.

 

[shg]

You're welcome, good luck.