+ Reply to Thread
Results 1 to 6 of 6

Lock conditional formatting but allow editing

  1. #1
    Registered User
    Join Date
    04-11-2019
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    3

    Lock conditional formatting but allow editing

    Hi,

    I have created some conditional formatting for certain columns in my worksheet which apply to all rows, i.e. Applies to=$A:$A

    But, whenever I copy and paste a new row, additional rules will be created automatically in my conditional formatting and the "Applies to" will be messed up.

    Tried the "Protect sheet" + Format cells>Protection>uncheck Locked, but unable to achieve my objective.

    Please help.

    Thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Lock conditional formatting but allow editing

    Would using Paste Special -> Values work for you? That shouldn't create new conditional formatting rules.

  3. #3
    Registered User
    Join Date
    04-11-2019
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    3

    Re: Lock conditional formatting but allow editing

    Hi Melvosh,

    Thanks for your advice, unfortunately it doesn't work for my case.

    I need to paste the same value+formulas from copied cell into the new cell but I do not want any changes to all my conditional formatting.

    Regards

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,673

    Re: Lock conditional formatting but allow editing

    If you Paste Special -> Paste Formulas it will paste values and formulas without changing formatting.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, 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
    Registered User
    Join Date
    04-11-2019
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    3

    Re: Lock conditional formatting but allow editing

    Hi 6StringJazzer,

    In most cases, I need to copy the whole row and use "Insert Copied Cells" to paste a new row.

    The conditional formatting rules will get affected.

    Regards.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,673

    Re: Lock conditional formatting but allow editing

    In that case you will need to first insert a blank row, then copy the existing row and Paste Formulas to the new blank row.

    Your only other option is to create a custom solution with macros.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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