Best practice for managing Conditional Formatting in a table?

[Magnatolia]

Hi all,

I have a problem where I have a 3000 row, 30-40 column table. In all there are about 30+ named ranges, about 10-15 columns of formulas that have between 2 and 5 nested formulas. And about 30-50 correct Conditional Formats.

When the CF's are correct, it goes well. But some records need to be duplicated with a new action number. It's when I copy these that my CF's get broken and duplicated and slows things down. I've tried pasting them beneath the table, pasting special for the conditional formatting, then extending the table.

Is there a better way for me to do this? I'm thinking that because I filter down to just the ones that need to be duplicated, I could increase the table by this number of rows and Paste-Formulas. Would this prevent the CF's from duplicating and breaking? Or is there a better way?

Thanks!

 

[Fazza]

hello

AFAIK this will be version dependent. Such as, Excel 2003 handled this without problem AFAIK & in later versions not so well. I'm not familiar with all versions though.

I suspect when there are problems one good way to handle it would be to have VBA. Such as code run on worksheet activation or via a button or similar that (1) removes all conditional format, and then (2) applies it. Perhaps using one worksheet to store in a table the rules and where they apply: then the code could loop through and apply formating based on the tables contents. (This is just an idea, I have no code to do that.) Or simpler just to hard code the conditional formatting rules within the code.

Maybe using the macro recorder would get you much of the way to having some code?

regards, Fazza