Conditional Formatting Rules can easily be disrupted. The same thing happens with Data validation ranges. Part of the solution is to use Excel Tables, if possible. Then, use VBA to make sure the rules are fixed every time you save the file.

UPDATES TO THE CODE on the video:

Add the following line of code to ensure that Data Validation rules are also preserved:

Selection.PasteSpecial xlPasteValidation

Derek Prince’s contribution in the comments: I mentioned that the template (worksheet CondFormat) would be hidden to prevent other users from accidentally corrupting the format. However, when we do that, we will also need to modify your macro to first Unhide this sheet, copy the format, apply it to the Data worksheet, and then Hide the template again. You can, of course, use “Application.ScreenUpdating = False” to hide these worksheet manipulations.