Condition Formatting

[SeniorTom]

question 1:

I have inherited a spreadsheet that has multiple conditional formatting rules. The first 10 are what I want. There must be near 100 rules that have developed over time (not likely intended), mostly one or two cell ranges. Is there a way I can clear the unwanted rules without doing it one at a time?

Question 2:

My range is F6 through DM130 and I add columns/row and remove columns/row as the year progresses. Is there a way, in conditional formatting to define a dynamic range to accommodate the changes?

Regards,
Tom

 

[AFPathfinder]

If the 10 rules you want to keep are in a range by themselves, just highlight the rest of the ranges that need the conditional formatting to be removed and select Clear Rules → Clear Rules From Selected Cells. Otherwise, you could remove them all and rebuild the 10 you want to keep.

I believe if you are inserting new columns/rows in-between the first and last column or row, the conditional formatting should be copied from left (column) or above (row). However, if you are adding to the ends, you may need to manually adjust the "Applies to" ranges. VBA may be able to assist if you know the specifics of where those formatted areas are supposed to be.

 

[SeniorTom]

Thanks AFPathfinder,

I went with delete all and recreate the 10. I also find if I take the first column that has the rules "column F" and copy it after the last column, the range expands. Thanks for your help.

Tom