An intelligent way to change colours in conditional formatting (excel 2013)

[VCare123]

Hi Everyone


I am faced with a task of replacing colours with different shades.
The demanding part lies in the conditional formatting, which turns out to be highly convoluted, most probably as a result of much reorganising to the rows and columns done after the conditions were set up.


To give you an idea of the number of conditions at play, today, I have worked on one file, and in 6 hours I have 'fixed' the reds and ambers in one of the bigger reports. Some sheets span from a1 to bs180, many columns in some sheets are hidden, some formulas exceed in length the text field they show in, and I simply cannot grasp the big picture of what happens there.
The reason why I have tried using brute force method is because the attempts I made to do it the smarter way, such as to use the Replace option or to apply custom colours failed. Another, much more experience user of excel has tried as well, but did not come up with any conclusions.


Faced with the perspective of doing greens in probably the same amount as reds (before I proceed to other files), I am desperately looking for a possibly error-proof but smart way to approach the problem?


I would greatly appreciate your help.


Thanks
Tom

 

[mole999]

you can edit a CF to cover an entire column and the. do that in the first row, then select the rest of the rows and delete the formatting, you may have to redo the first row again but that should do the entire sheet. Test on a copy first