How to extract formatting from specified cells to create conditional formatting rule via VBA?

Question

Hi

There have too many conditional formatting distributed in different worksheets to make it slow to run, and often has errors and duplications due to adjustments, as well as lead to closure, as shown below:

This is a sample file as reference:

https://1drv.ms/x/s!AibaAz5STOvFiCZS2sVJW80fOKx9?e=3DUWPh

I want to optimize all conditional formatting via VBA. To make it easier to create and use new rules, I want to use VBA to extract formatting from specific cells and then run to create conditional formatting rules and apply to selected cells based on matching text.

For example, some cells in the picture below are highlight by different colors. In order to be recognized and run in VBA easier, I defined name group for them separately.

I hope the vba functions should belike this:

• Create range object
• Delete previous conditional formats
• Create new rules and apply to selected cells based on matching text.
• Can be toggled to create or delete above rules

How can I implement this function please? or o you have any better idea?

Many thanks.

J.