How to Fix broken Conditional Formatting Rules with VBA
2.8 هزار بار بازدید -
6 سال پیش
-
Conditional Formatting Rules can easily
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:
https://solveandexcel.ca/2020/08/25/f...
--------------------------------------------------------
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 YouTube Channel – @djangoflyfar2633
Icons made by Alfredo Hernandez - https://www.flaticon.com/authors/alfr... from FlatIcon - https://www.flaticon.com
If you need to contact me: https://solveandexcel.ca/
See blog post about this topic:
https://solveandexcel.ca/2020/08/25/f...
--------------------------------------------------------
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 YouTube Channel – @djangoflyfar2633
Icons made by Alfredo Hernandez - https://www.flaticon.com/authors/alfr... from FlatIcon - https://www.flaticon.com
If you need to contact me: https://solveandexcel.ca/
6 سال پیش
در تاریخ 1397/04/10 منتشر شده
است.
2,827
بـار بازدید شده