Cleaning up Conditional Formatting

[Hydestone]

I've got a spreadsheet which has been very reliable over the past few years. However, recently is has become a little unstable and periodically crashes for an unknown reason. I think it might be related to conditional formatting that has slowly gotten out of hand as rows have been copied and inserted etc.

Is there an easy way to hunt through and clean up conditional formatting?

 

[DRSteele]

It sounds as though CondFrmt is the culprit. Delete all the rules and apply them again en masse across the proper ranges. In my experience all that copying, moving, inserting and deleting of rows & columns ends up creating duplicate or overlapping rules scores of times and it gets completely unintelligible, not to mention sluggish.

 

[DanteAmor]

CF take up too much memory.
It may be necessary to pass some CF to macro.

 

[Hydestone]

Thanks folks.

I've got 20-30 people around the office using the spreadsheet, which is not macro-enabled.

I'll tidy up the CF and see if that helps.

 

[Joe4]

One thing I have done on some templates I have created for staff is to limit the CF to a set number of rows (like 1000), and then use sheet protection to block the ability to use any rows below that, and disabled copy/paste functionality (also through protection). That helps limit the ranges that CF is applied to, and helps keep it from getting all out of whack. As you probably already know, copy and paste will overwrite CF rules.