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

This isn't very comforting, but appreciate the feedback

Answer 3

The ClearConditionalFormatingBeforePaste() didn't work.  Keep getting error 400

I selected the whole sheet from the FROM (clean) file, then went to the RECEIVING file, clicked the top left, then ran macro -- didn't like it. 

But sounds like it has promise!

I do have a mix of CF, merged fields, etc.

Answer 4

Hi, a little late to the discussion, but I have a similar problem.  

How about Paste Special, and choosing Values?  This seems to be helping me.  If using a macro, you should be able to paste as values there also, and therefore avoid pasting a new set of conditional formatting rules.

I can foresee some times that I won't be able to do this though...  I might have to try mucking around with sheet protection.  The biggest problem I have is that I won't be able to give my spreadsheets to a less savvy Excel user.

I'd be keen to hear if you've found any other tricks.

K.

Answer 5

I just had the same issue and figured out how to do a fairly quick / easy fix.

1. Paste all my data however was fastest/easiest.
2. Make sure your first line of data is formatted correctly,
3. then selected all but the first line of data
4. remove all conditional formats.. if you really want to be clean, remove all formatting as well
5. select that first line of data and do a copy format (paintbrush)
6. then select all lines of data - including that first line - to apply the formatting to the whole batch.
7. Done!

Kati