Excel VBA: Conditional Formatting 'Applies To' keeps changing...

  • I manipulate an output from a scheduling program to form an array in the appropriate order. Then I paste that array into a Worksheet that is then used to make daily work assignments. That Worksheet contains Conditional Formatting to ensure that our 'Assignment Rules' are met.


    Issue:

    The damn Conditional Formatting (CF) keeps getting broken up into covering only a portion of the Worksheet that the CF is supposed to cover. For example, instead of one CF Rule covering $A$3:$E$35, it will get broken up into a number of smaller arrays instead of all of $A$3:$E$35, for example there will be two rules that are the same but "Apply To" different ranges, such as $A$3:$B$35 and $C$3:$E$35. Occasionally it also skips a portion of that total array $A$3:$E$35. Sometimes it will also break down a rule such that instead of it covering an an entire range, it will duplicate that one Rule and Apply it to individual cells. If I try to protect the cells, it won't let my VBA paste the array in the first place. I feel that I have too many CFs to write each of them into a VBA script in order to apply them via a Macro.


    Is there a way to preserve Conditional Formats in VBA? Why is this happening? I'm not dragging and dropping cells throughout the Worksheet.