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.
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.