+ 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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Lock conditional formatting but allow editing

    If you Paste Special -> Paste Formulas it will paste values and formulas without changing formatting.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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)

Similar Threads

  1. Lock but view conditional formatting
    By excelquestions1234 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2018, 03:08 PM
  2. Can I lock a conditional formatting range?
    By Kevin4fm in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2018, 04:00 PM
  3. [SOLVED] Lock Down Conditional Formatting
    By ballard.ric in forum Excel General
    Replies: 1
    Last Post: 11-16-2017, 02:00 PM
  4. 'Lock' conditional formatting
    By JoHansell in forum Excel General
    Replies: 3
    Last Post: 10-31-2013, 02:30 PM
  5. Conditional formatting - space for editing rule
    By nsv in forum Excel General
    Replies: 1
    Last Post: 11-07-2011, 10:09 AM
  6. Conditional Formatting to Lock a Cell
    By Lizabeta in forum Excel General
    Replies: 6
    Last Post: 04-29-2009, 05:14 PM
  7. Conditional Formatting Not showing without editing cell
    By mcnaught@lincoln in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2005, 06:05 PM

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