Copy & Paste without removing conditional formatting

**Barking_Mad** · 03-16-2010, 08:28 AM

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

**martindwilson** · 03-16-2010, 08:31 AM

i think you have that the wrong way around a copy /paste will copy all formating paste values doesn't copy formatting

**Barking_Mad** · 03-16-2010, 08:58 AM

Sorry - people are copy and pasting names from one sheet without conditioanl formatting into a new one, and this is resulting in the conditional formatting being erased.

Aside from them doing COPY > PASTE VALUES, is there another way to allow them to paste the information and keep the conditional formatting ive set up in the new sheet?

Hope that makes sense

**6StringJazzer** · 03-16-2010, 09:37 AM

Makes perfect sense

You want to prevent users from clobbering your conditional formatting by pasting on top of it.

I could not find a simple way to do what you need, though I have learned not to say things are impossible. Typically user behavior is limited by using protection, but protection options are not granular enough in Excel for this particular situation. You can lock a cell and protect the sheet, and the user cannot change the formatting, but then the user cannot change the value either. If you unlock the cell, then the user can change the value, but can also change the formatting. I confirmed my understanding of this with some experiments.

One option would be to write some VBA based on the Worksheet_Change event. This event is triggered when data changes on a worksheet. You can't prevent the user from pasting a format, but you can write code that will at least detect the change, and can re-establish the conditional formatting. This is straightforward but a bit challenging if you haven't written VBA before. Also, the user must allow macros to run.

**martindwilson** · 03-16-2010, 10:12 AM

dave hawley posted a possible work around basically copy the formats to another sheet
when something is changed they are then copy/pasted back
http://www.ozgrid.com/forum/showthread.php?t=57202 but as
6StringJazzerusers have to enable macros if thet dont they could paste away to their hearts content

**Barking_Mad** · 03-16-2010, 11:00 AM

Thanks for your help, both of you. Ill class this as solved as I can do the rest....

**6StringJazzer** · 03-16-2010, 07:58 PM

Originally Posted by martindwilson

dave hawley posted a possible work around basically copy the formats to another sheet
when something is changed they are then copy/pasted back
http://www.ozgrid.com/forum/showthread.php?t=57202 but as
6StringJazzerusers have to enable macros if thet dont they could paste away to their hearts content

Not sure why Dave used Worksheet_SelectionChange instead of Worksheet_Change, since the former will cause formats to be re-copied every time the user clicks on a different cell, even when nothing changes. It's probably fast enough that the user wouldn't notice, though.