+ Reply to Thread
Results 1 to 7 of 7

Copy & Paste without removing conditional formatting

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    266

    Copy & Paste without removing conditional formatting

    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
    Last edited by Barking_Mad; 03-16-2010 at 11:00 AM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy & Paste without removing conditional formatting

    i think you have that the wrong way around a copy /paste will copy all formating paste values doesn't copy formatting
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    266

    Re: Copy & Paste without removing conditional formatting

    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

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    13,265

    Re: Copy & Paste without removing conditional formatting

    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.
    Jeff

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Copy & Paste without removing conditional formatting

    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

  6. #6
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    266

    Re: Copy & Paste without removing conditional formatting

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

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    13,265

    Re: Copy & Paste without removing conditional formatting

    Quote Originally Posted by martindwilson View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1