How to Fix broken Conditional Formatting Rules with VBA

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.
See blog post about this topic:
solveandexcel....
--------------------------------------------------------
UPDATES TO THE CODE:
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.
------------------------------------------------------------
Mackenzie Cook’s KZbin Channel - / @djangoflyfar2633
Icons made by Alfredo Hernandez - www.flaticon.c... from FlatIcon - www.flaticon.com
If you need to contact me: solveandexcel.ca/

[@pickleball_dereck]

Much appreciated, Celia. You correctly mentioned that the template (worksheet CondFormat) would be hidden to prevent other users from accidentally corrupting the format. However, when you do that, you 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 maniuplations. Thanks for sharing!

[@faisalakbaradin6352]

Thank you so much Ms. Celia, this really helps my work!

[@CeliaAlvesSolveExcel]

Awesome, Faisal! Happy to help and thank you for leaving your feedback. Please check additional tip in the video description.

[@GuiadoExcelOficial]

Parabéns pela solução, realmente interessante copiar os formatos á partir de uma tabela pré-formatada, realmente facilita e reduz a complexidade do código fonte. Ajustar também ao salvar foi uma boa sacada. Forte abraço Marcos Rieper

[@CeliaAlvesSolveExcel]

Obrigada, Marcos! Esse comentario vale muito, e vindo de voce ainda vale mais. Abraco.

[@OzduSoleilDATA]

WOW! This is a serious concern to address and a nice solution. These kinds of problems are scary because users often don't know just how fragile conditional formatting and dropdown lists can be.

[@juliennadon4134]

This is amazing, I've spent countless hours trying to fix this problem. Thank you so much!

[@CeliaAlvesSolveExcel]

You're very welcome! Glad that it helped and thank you for stopping by.

[@patricknicholson5556]

Where can I get the VBA code?

[@CeliaAlvesSolveExcel]

For this one, you'll have to follow the video and type the code as you watch. Please check one update for the code on the video description.

[@briantwitchell]

Does the macro add a new set of rules to the sheet every time you update it? I am having a related but different issue. I have a sheet that has new lines being added all the time, and every time they are, a new set of rules is being added to the spreadsheet and we are in the 100s by now. Can you write a macro that calculates the number of rows and then edits each rule to extend them to the entire sheet?

[@CeliaAlvesSolveExcel]

Hi, Brian. This macro removes all the rules (to remove rule duplicates) and reinstates them. I suggest you follow the video. You can also check the blog post on my website about the same topic. See video description.

[@morilia1669]

Ola Célia, bom dia! Eu vim do canal da Vivi e já me inscrevi em seu canal. Eu sou um apaixonado por Excel, pena que não entendo Inglês mas vou tentar ver se aprendo um pouco.

[@blade12]

This would have been very useful for me, but my spreadsheet has merged cells that the format painter breaks

[@CeliaAlvesSolveExcel]

Merged cells are the worst! See if you can use Horizontal Alignment "center across selection" instead.