Condition Formatting

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
96
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top