+ Reply to Thread
Results 1 to 5 of 5

Locking conditional formatting but allowing entry into the cell??

  1. #1
    Registered User
    Join Date
    04-12-2012
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2016
    Posts
    22

    Question Locking conditional formatting but allowing entry into the cell??

    Hello -
    I have a spreadsheet that is used for data entry. I have added conditional formatting that when someone enters a number into the cell, it turns red, yellow or green depending on the value entered. I want to be able to lock everything on the spreadsheet (conditional formatting & changing the spreadsheet setup) except for the data entry into that cell and a comments cell. I would like it so that all the operator can do is enter the value and enter comments into the cell next to it. I've searched and played with the protect sheet options but haven't had any luck yet. Can someone help me? Thank you!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Locking conditional formatting but allowing entry into the cell??

    The default value for all cells is "Locked" so when you Protect a spreadsheet, all cells become locked.

    To unlock cells, on an Unprotected sheet, select the range of cells you want to change and right click within the range > "Format cells..."
    Then go to the Protection Tab on the opened window and uncheck "Locked" > Okay
    Done
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Locking conditional formatting but allowing entry into the cell??

    1. Set all the cells on the sheet as locked apart from the two in question (use cell formatting - security to do this).
    2. Protect the sheet.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    04-12-2012
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Locking conditional formatting but allowing entry into the cell??

    thank you, that did work and I can not change the conditional formatting. However someone can still cut and paste and move the conditional formatting. Is there away to remove the cut/copy function for the unlocked cells so that they have to manually enter a number each time?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Locking conditional formatting but allowing entry into the cell??

    No easy way sadly, especially since there are so many different ways to cut and paste. It would require using VBA so the user would have to enable macros when they open the sheet. The best way is through training. Make it clear through training and in the spreadsheet itself (through instructions, data validation input message, and/or cell comments) that they are to either Paste Values or manually input and not to cut and paste.

+ 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. Conditional formatting based on a cell entry type...
    By Finalfrontier1976 in forum Excel General
    Replies: 1
    Last Post: 10-31-2014, 03:47 AM
  2. [SOLVED] Protecting Cell Format but allowing cell data entry
    By telcocook in forum Excel General
    Replies: 3
    Last Post: 10-10-2014, 04:06 PM
  3. Allowing manual entry into a cell but maintain cell formulas
    By Cipher Coder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2013, 04:50 PM
  4. Conditional Formatting a cell based on an entry into another cell
    By brettolma in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-25-2012, 04:19 PM
  5. Cell with VLOOKUP allowing manual text entry aswell?
    By steve_l in forum Excel General
    Replies: 1
    Last Post: 11-22-2011, 07:12 AM
  6. Visual Basic - Not allowing cell entry
    By emad1001 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-21-2008, 03:47 PM
  7. Conditional formatting of an entire row based on a cell text entry
    By Gilles Desjardins in forum Excel General
    Replies: 2
    Last Post: 12-22-2005, 07:55 AM

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