Possible to restore conditional formatting with VBA ?

[JumboCactuar]

Hi,
People who use my workbook seem to find ways of breaking or moving conditional formatting.

I set a macro to CTRL+V to paste values only but this doesnt prevent pasting using the button.

Can you somehow save all conditional formatting of the current sheet, and restore it with VBA if needs be?

 

[rlv01]

There are a couple of ways to approach this. One is to store a pristine copy of your main worksheet as a hidden worksheet, and then use code like this

Worksheets("MyHiddenSheet").Cells.Copy
Worksheets("MyVisibleSheet").Cells.PasteSpecial Paste:=xlFormats

to restore formatting when required.

Another method is to code it. A format condition is an object in the excel object model, so it is possible to write a VBA routine that would (for example) delete all format conditions, including any that your users have messed with, and then rebuild them to their original condition. You can use the macro recorder to add a couple of format conditions and get a pretty good idea of the needed syntax.