Conditional Formatting Rules keep building up

Question

I have a worksheet that I use as a template.  Many of the cells have specific conditional formatting rules.  I send the template out to others for update, they return, and then I COPY/PASTE the entire worksheet (using the upper lefthand corner) onto my master workbook.  Over time, though, each cell that has conditional formatting rules, begins to grow.  Let's say I have 3 rules for cell A3 -- if "R", then color Red, if "Y", then color Yellow, if "G" then color Green.  But when I copy/paste and go into cell A3 and look at Manage Rules -- I will now have 6 rules -- the original 3 replicated. The problem is that over time, the file continues to grow in size because of these 'growing rule sets'.     Short of periodically going into Manage Rules and deleting all the repeats -- is there another way?    (Note, it doesn't happen with Copy/Paste Values but that is not an option for the entire spreadsheet.  I don't want to have to copy section by section because some sections include Merged cells which pose their own issues -- the COPY/PASTE of the entire worksheet overcomes that, and also maintains the height of the cells if the end-user has added information that requires the height to change).

PLEASE ADVISE.  Its driving us crazy ---- Thanks.

Answer 1

Pam,

Think you misunderstood.  The ClearConditionalFormatingBeforePaste macro should be used INSTEAD of paste to stop the c.f.s building up.  You must copy something from a (clean*) FROM sheet first, then go to the RECEIVING sheet/file, and the click where you would if you were going to paste, but run the macro instead.

*clean = only one set of c.f., not "built-up"

So, first, make clean FROM file.

Second, clean all the other FROM files by (a) clearing all c.f. from the entire sheet then (b) using the format painter to reaply the  c.f. from the first clean file.

Third, from now on, use the macro instead of paste when copying cells that have c.f. applied, to stop c.f.s building up.

Hope that helps.

Cheers

Rich

PS. Let me know if you need to know how to add a button to run the macro to the Quick Access Toolabr.

Answer 2

I have a worksheet that I use as a template.  Many of the cells have specific conditional formatting rules.  I send the template out to others for update, they return, and then I COPY/PASTE the entire worksheet (using the upper lefthand corner) onto my master workbook.  Over time, though, each cell that has conditional formatting rules, begins to grow.  Let's say I have 3 rules for cell A3 -- if "R", then color Red, if "Y", then color Yellow, if "G" then color Green.  But when I copy/paste and go into cell A3 and look at Manage Rules -- I will now have 6 rules -- the original 3 replicated. The problem is that over time, the file continues to grow in size because of these 'growing rule sets'.     Short of periodically going into Manage Rules and deleting all the repeats -- is there another way?    (Note, it doesn't happen with Copy/Paste Values but that is not an option for the entire spreadsheet.  I don't want to have to copy section by section because some sections include Merged cells which pose their own issues -- the COPY/PASTE of the entire worksheet overcomes that, and also maintains the height of the cells if the end-user has added information that requires the height to change).

 

PLEASE ADVISE.  Its driving us crazy ---- Thanks.

Maybe, delete all the c.f.s in the workbook/sheet *from* where you will be copying before doing the copy?

Home tab | Conditional Formatting drop down > Clear Rules sub menu > Clear Rules from entire sheet button.

Answer 3

When I delete the CF from the "FROM" file and copy/paste into my master - it removes the CF from the "RECEIVING" file (master).  However, if I removed the CF from the MASTER, then copy in -- (assuming that my FROM file doesn't have duplicates) it works.  But here's the problem -- I have 40 "from" files out there each with a lot of CF cells that I will now need to clean up -- is there any function or macro or anything that could help???

Why do the CFs build up -- is this a problem that will be fixed in the future???

Answer 4

When I delete the CF from the "FROM" file and copy/paste into my master - it removes the CF from the "RECEIVING" file (master).  However, if I removed the CF from the MASTER, then copy in -- (assuming that my FROM file doesn't have duplicates) it works.  But here's the problem -- I have 40 "from" files out there each with a lot of CF cells that I will now need to clean up -- is there any function or macro or anything that could help???

 

Why do the CFs build up -- is this a problem that will be fixed in the future???

I suspect the reason Excel creates duplicate c.f.s is the same one that I am reluctant to say there's a macro solution to the problem.  While a duplicate c.f. may be easy for you to identify, particularly in the context of your task, it does not seem to be that easy to do programmatically.  That's not to say it cannot be done, just that there doesn't seem to be a *simple* programmatic solution.

As to why c.f.s build up, it is probably because of the complexity of identifying a duplicate c.f.  There are simply too many variables (Number format, Font, Border, Fill, etc.) that one would have to test. 

As to whether this is a problem that will be fixed in the future, I don't know if Microsoft considers it a problem and if it does I don't know when or even if there will be a change.

Answer 5

Acknowledging what Tushar has said above, if you only have conditional applied to specific cells, i.e. you do not have various ranges that overlap, each with their own c.f, and if you just want to clear the c.f. from the selected cells, you just need a macro with this line:

Selection.FormatConditions.Delete

Or, you can just go Home tab, Styles group, Conditional Formatting, Clear Rules, then right click on Clear Rules from Selection and Add to Quick Access Toolbar.  You've then got easy access to that command.

You could "clear up" the duplicates in one file, delete all the c.f. in the "FROM" files using a macro running the above code, then use the format painter to copy and reapply the c.f. from the one "cleaned" FROM file to all the others.

In the future, you could easily make a macro and add a button for it to the QAT that you would use instead of Paste:

Sub ClearConditionalFormatingBeforePaste()

    ActiveSheet.Paste

    Selection.FormatConditions.Delete

    ActiveSheet.Paste

End Sub

This pastes the data, including c.f., and the selection will expand to cover the range that was pasted.  Then the c.f. from that range is cleared, then you paste again leaving only the c.f. from the FROM file. 

Hope that helps (please let me know as I have this problem too!)

Cheers

Rich