Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells
There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.
Here is how you want to present your worksheet to your users, so they know where to enter data:
Users appreciate seeing exactly where they can — and should — enter data.
You can conditionally format unlocked cells with this boolean (True or False) formula:
=CELL("PROTECT",A1)=0
The above formula is what is being used in this example, shown in the following pictured steps.
As a quick aside, you can conditionally format locked cells with this True/False formula:
=CELL("PROTECT",A1)=1
Although not absolutely necessary, it is advisable 99.9% of the time that you protect your worksheet after you have installed the conditional formatting. This will ensure that users will only have access to the unlocked cells.
Step 1 — Select the entire used range.
Step 2 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Select the option for Locked.
• Click OK, to make sure all cells in that range are locked.
Step 3 — Select the range(s) where you want to unlock the cells for users to enter data.
Step 4 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Deselect the option for Locked.
• Click OK.
IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:
Step 5 (version 2003 or before) — Press Alt+O+D to show the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.
Step 6 (version 2003 or before) — In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.
Step 7 (version 2003 or before) —
In the Conditional Formatting dialog box, click OK, and you are done.
IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:
Step 5 (version 2007 or after) —
Press Alt+O+D to show the Conditional Formatting Rules Manager dialog box:
• In the “Show Formatting Rules for” field, select Current Selection.
• Click on the item labeled “New Rule”.
Step 6 (version 2007 or after) — In the New Formatting Rule dialog box:
• Select “Use a formula to determine which cells to format”
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.
Step 7 (version 2007 or after) — In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.
Step 8 (version 2007 or after) —
In the Conditional Formatting Rules Manager dialog box, click OK.
Step 9 (version 2007 or after) —
In the New Formatting Rule dialog box, click OK, and you are done.
Is there a way to lock/unlock a cell based upon the contents of another cell. For example, if the 2nd Quarter cells were locked unless data was entered in 1st Quarter?
Yes, but it would require VBA. Let me know if that is acceptable for your workbook and the people using it.
Dear Mr. Tom Urtis,
Kindly send me the sample excel formula for Is there a way to lock/unlock a cell based upon the contents of another cell. For example, if the 2nd Quarter cells were locked unless data was entered in 1st Quarter?
bsafm69@yahoo.com
A formula alone will not accomplish what you want. You need VBA. And because you need VBA, you do not need a worksheet formula at all, but rather, a worksheet change or calculate event (depending on what data type is in the cells belonging to the 2nd Quarter) to monitor what is missing in the first quarter.
I’ve done the conditional formatting, the only bad part of that is if you subsequently protect or unprotect a cell later, you must rerun the conditional formatting. Is there a way for the color to change automatically without having to rerun the conditional formatting once you’ve protected or unprotected the cell?
Re: request to lock/unlock cell based on value in another cell. Would validation work? It could prevent entry based on condition without truly locking. Same effective result but no VBA. ?
No, it is different from locking cell : you can always press delete to clear the cell without activating data validation.
Is there any way to lock the conditional formatting? So that when people cut & paste their data into my spreadsheet, that it keeps the Conditional Formatting rules that I had set for those cells to begin with?
If the action is a cut and paste, then by the very nature of what a paste does, it overrides all the properties of the destination cell(s). If you copy and paste special for values, that should get the result you’d want, to maintain the formatting.
Interesting and clever solution Tom. Wonder if you can help me. I am trying to lock cells that I have conditionally formatted. I cannot manually lock them because the cells change when the data is refreshed. (New data is pasted into another sheet). My cells display drop down lists dependent on contents of cell offset to the left. Some need to display a drop down, others needs to be locked. I have written a formula in Data Validation DB =IF($F6=””,Desks!$A$1,SundayDesks)
Where the result is “” I would like the cell to be locked.
Locking and formatting Desks!A1 does not help.
Hello Penny – –
Can you clarify please, do you want all cells that are conditionally formatted to be locked, or do you want to only lock cells that are conditionally formatted and have some value (or no value) in them. If the answer to that question is, you want the latter and the cell to be empty (a “” null string), does that mean that forevermore there shall be no possibility (because that cell would be locked) of having a value be placed into that cell down the road?
I would like to know if it is possible to create another conditional format rule for all cells to the right of my column with values in it that will highlight the cells for each line of data to change color based on the data column?
Reading your comment carefully, I want to make sure you don’t mean what it almost sounds like you are asking, which is to conditionally format a particular cell that would affect a range of other cells, which cannot be done unless you use a VBA event (not native conditional formatting) to accomplish that. Please clarify what you are actually trying to do.
Is it possible to conditionally unlock a cell which contains a specific data entry. I.e. In my case the text SPACE. ?
The background is that In a room booking system I have a column where the data contains locked data , the contents of which are derived by a formula relating to a persons name in a previous entry in an earlier row. If the room is available the first entry will be assigned “SPACE” and this is replicated in a cell lower down in the column because of the formula in the cell. So at any future row (time slot in this case) the space might want to be changed from “SPACE” to the name of a particular individual.
I almost followed your scenario but not 100%.
First, take a close look at your post.
Is the string of interest…
SPACE. (with a suffixed period or dot character)
or
SPACE
Second, can you give a specific example of a cell (that is, please specify a cell for example) that gets changed (I **think** manually by someone entering it on their keyboard, but that’s what I need to know), AND, a cell that contains a formula replicating that entry.
What I don’t get is, why not data validate the manual entry cell for only “SPACE.” (or “SPACE”)?
I should perhaps qualify that I am not a programmer let’s say an enthusiastic amateur! What I have produced is a bookings system spreadsheet for my keep fit instructor. The bookings system caters for 5 days a week Monday to Friday. It has has 5 hourly slots per day and runs for 7 weeks (a phase). The layout of one hours session is as follows:-
DATE NAME STATUS
19 Jun. Fred. Booked
Mon. John. Booked
7.00 am. Peter. Booked
Free slots. Ian. Booked
1. SPACE. Free
Booked
4
The class caters for 5 attendees so if there is a slot free then SPACE is entered in the name cell. For the training phases the names and times of people attending in the main stays the same. In the NAME column the two cells at the bottom are not locked and can be used by the instructor to add a name if one of the regulars is either away or ill. Drop downs in Status cells enables this to be recorded (Booked, Holiday, Ill).
So for the first week name cells they are all unlocked BUT for week 2 the name entry is simply =S20 where S20 is the cell reference for the corresponding time slot and name row position for the first week. Week 3 sees another formula entered say =S42 where S42 is the cell reference for the for the corresponding time slot and name row position for theprevious week. This is replicated for the rest of the weeks in the training phase.
With this design the names of attendees are automatically replicated for weeks 2 through to week 7 saving considerable data entry time.
The issue I am having is that if there are any SPACE entries in any of the week 1 cells these are replicated throughout subsequent weeks. When someone joins and takes up the SPACE currently the only way of entering their name by typing over the SPACE entry is to unlock the spreadsheet.
This is why I should like to be able conditionally unlock any Cell with an entry SPACE.
Hope this is clearer.
It sounds like cells that contain SPACE are locked at some point, and maybe they are locked as soon as you enter either a name or the word SPACE. Therefore, it sounds like when you want to enter anything in the NAME column, you need to unlock the worksheet. If that last sentence is not correct, then it definitely sounds as if when you want to enter something (presumably, someone’s name) into a cell that contains SPACE, you somehow want to have that cell accept that entry without you needing to manually unlock the worksheet and then re-lock it after the non-SPACE entry is made.
Please confirm if I have that correct so far, and also tell me if VBA is OK to employ here, which you will need. You don’t need to do anything extra except to save your workbook as a .xlsm extension type. I’d give you the code I think can work but first I need to know if my comprehension is correct as to how your worksheet operates, and if VBA is acceptable. You cannot otherwise edit a locked cell by first unprotecting the worksheet manually — then reprotecting it after the edit — unless programming code is employed.
Yes you are correct about the locking – all cells in the name column are locked except the first week. Not done any VBA but it is something I have wanted to do. So giving it a go with a spreadsheet I am familiar with would be great.
Any pointers on how I might solve the problem using VBA. I have a number of macros running in the spreadsheet created in developer mode. I can see the VBA listings for these but I created them by doing the excel key commands rather than writing in code.
Right click on your worksheet tab of interest, left click on View code, and paste this into the worksheet module. Modify for password because it probably isn’t hello, and modify if need be for column number if it isn’t column S which is column 19. Hit Alt+Q to return to the worksheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim myPassword$
myPassword = "hello"
If Target.Column <> 19 Or Target.Cells.Count > 1 Then
ActiveSheet.Protect "myPassword"
Exit Sub
ElseIf Target.Value <> "SPACE" Then
ActiveSheet.Protect "myPassword"
Exit Sub
End If
Dim myConf%
myConf = _
MsgBox("Do you want to enter a name" & vbCrLf & _
"in cell " & Target.Address(0, 0) & " instead of SPACE?", 36, "You selected a cell with SPACE.")
Select Case myConf
Case 7
MsgBox "No problem, nothing will change" & vbCrLf & _
"and the sheet will remain protected.", 6 + 4, "You clicked No."
Exit Sub
Case 6
MsgBox "OK, go ahead and edit cell " & Target.Address(0, 0) & ", then" & vbCrLf & _
"select any cell to re-protect the sheet.", 64, "You clicked Yes"
ActiveSheet.Unprotect "myPassword"
End Select
End Sub
Dears, I need your help.
I’m working on a portfolio of several projects.
Meanwhile, there are some data to be entered related to a specific Project only. So I prepared a log for the whole portfolio, but I need to lock the cells which related to specific project while unlocking on the project.
Can you advise regarding my Matter.
Regards.
The logic must be defined somewhere for the code to know which cells must be locked and other cells not locked. Also, once a cell is locked, would anyone be able to unlock them later to edit or change the locking rules.
Please help me on this, the dropdown list must be locked if the user already pick his/her choice in the list. While the Blank dropdown list are still unlocked. It will be locked if there is already a data from the user based on his/her selection in dropdown list.
Thank you, this will help me a lot.
You did not say what cell you want this to be for, so assuming it is cell A1 (modify the code accordingly), right-click the sheet tab where this is happening, left-click onto View Code, and paste this code into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. If you have not already done so, save the workbook as a .xlsm (macro-enabled) type. Also modify for whatever password you want the worksheet to have.
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address <> "$A$1" Then Exit Sub
If Target.Locked = True Then Exit Sub
ActiveSheet.Unprotect
If Len(Target.Value) > 0 Then Target.Locked = True
ActiveSheet.Protect
End Sub
Can we have method to automatically “unlock” (for any operation) a otherwise “locked cell” in protected sheet depending on value of another cell ?
Let’s say I have a worksheet in which all the cells are protected in column O.
My column D has drop down menu to select from @ 58 list.
What I want to achieve is if someone select from drop-down “subtotal” in any row in column D, respective cell in that row in column O shall be unlocked for subtotal.
How we can achieve this.
Looking forward and thanks in advance.
That is no problem, totally do-able, but you should specify a few things first:
– When and how (if ever) does that cell in column O get Locked again.
– What exactly does “@58” mean.
– What does “drop down menu” mean. Data Validation? An embedded activeX ComboBox? An embedded ComboBox from the Forms menu?
– What exactly is the word: “subtotal” the way you posted it here, or Subtotal or SUBTOTAL.
– Is the worksheet protected with a password.
Hi-
I was told by someone at work that if a worksheet gets unlocked, the conditional formatting coded in the worksheet might change. An unlocked sheet might do things not intended originally. I find that statement hard to believe. If no one is making changes to the unlocked form, just the act of unlocking the sheet might create changes, is that true?
Thanks!
Just to keep the terminology accurate, worksheet cells are formatted as Locked or not. The entire worksheet object gets protected or unprotected. So, despite what you heard that “if a worksheet gets unlocked”, it probably means “if a worksheet gets unprotected”. You cannot unlock a cell that is locked when the worksheet is protected. You need to unprotect the sheet first. Naturally then, with an unprotected sheet, any change can happen and all bets are off. Regarding Conditional Formatting per this example, an unlocked cell will have a different conditional format than a Locked cell, which makes sense and is as it should be.
Thank you, Tom!
I did not have the right terminology.
If an entire worksheet is unprotected, no one has touch it, will the conditional formatting (such as if the cell value is greater then 5, cell will be highlighted yellow) be lost or changed just because the whole worksheet got unprotected?
Of course all bets are off with an unprotected worksheet. But if I just unprotected it, I have not made any edits, should I be worried the conditional formatting rules I’ve previously set up be changed?
The short answer is no. Conditional Formatting functions the same if the worksheet is protected or unprotected.
Came across your tutorial as I was searching for a way to conditionally lock cells, hopefully in the same method that I can conditionally format cells. But it looks like this may not be possible.
Do you know of a way to do this? For example, if Cell A1=10, cell A2 becomes locked.
I understand what you are saying, and with what you describe, it is possible to do but only with VBA.
Hi Tom!
I wonder if you’d be able to help me out with something similar. I’ve been trying to find an answer, but the VBA one that comes up most often doesn’t seem to work for what I have in mind. Either that, or I don’t know how to edit it properly for it to work.
I have a table structured so, and most of the columns have Y/N dropdown selection:
Name Home? Task 1 Task 2 Task 3 Task 4 Task 5 Task 6
John Y
I’m trying to get the cells in columns C:H to either lock or unlock based on the value found in all the subsequent cells in column B. So, if B2 is Y , C2:E2 would be editable with Y/N, while F2:H2 would have their cells on row 2 locked.
So, if B2 = Y , C2:E2 unlocked and F2:H2 locked. If B2 = N, C2:E2 locked and F2:H2 unlocked. And so on for all the rows.
The main issue with the VBA code I found is that it locks selected cells based on the value of one single cell only. I tried to edit it to contain the whole column of cells, but it didn’t work.
Is what I’m trying to do even possible?
What you want to do is definitely possible. Can you please confirm that a VBA solution is OK for you, and if so and if you have tried VBA which it sounds like you have, what exactly was it — a macro or a worksheet event procedure or a workbook event procedure or a class module of a UDF. I just want to know what you did which maybe can be modified or maybe avoided, depending on what you tried.
I know this is over a year old but I hope you are still available for questions or can direct me to someone able to help.
I am looking for a away to have cell f2 be locked/stop entry only if cell c3 is less than a numeric value (let’s say 3). I then need to have i2 remain locked/stop entry if value of f2 is below 5 and so forth like a chain.
I have an area like above but I need i20 dependent upon f18, f21 and f24 if the total of them is less than 15 it remains locked/stop entry.
I need h9 or h12 to determine if data can be input into k11.
I have each of these entry areas mentioned above as a data validation list (not table just raw source). How can I ensure the droplist is interactive yet no way to accidentally edit the validation or formula? Also every ask in here of a function is ultimately for a protected sheet once all the editing is done.
The only thing that seems to work to prevent information from being added is with Data validation. There lies an issue with entering values into each spot as values outside of set parameters such as decimals, higher than a range, negative or even words can be entered. It is supposed to be whole integers between 0 and the max value of each cell (they are largely different.)
The drop-down that I created with data validation does not seem to be recognized as data input. Even if the next cell in the chain is set to validate if he Dropbox meets criteria to alloe data to be entered will not prevent data entry.
What this boils down to is I need a chain of entry cells that will only allow data if the link previous meets a threshold. Each cell is variable in its maximum allowable whole positive integers including adding 0 (if it exceeds this value or is not a whole number then it prevents data entry.) Some chains meet up and where they do the 2 or more cells prior might be the condition for allowing data to be entered into the met cell (sometimes the linked cell might need just a value of 1 to be in 1 of 2 or more cells.)
What you want to do is almost certainly do-able.
What is not do-able is understanding everything you are talking about:
• “and so forth like a chain” can mean out to column XFD.
• “I need i20 dependent upon f18, f21 and f24 if the total of them is less than 15 it remains locked/stop entry” is unclear about how the numbers get into F18, F21, and F24 (manual entry or copy/paste or by formula.
• “I need h9 or h12 to determine if data can be input into k11.” No idea what in the world that means.
I suggest you post this whatever-it-is question on an Excel forum https://www.mrexcel.com/board/forums/excel-questions.10/ that I and other Excel experts monitor around the clock. Sign-up is free and no spam. You will get a faster and more relevant response than I can provide, especially because the clarifications you still need to supply.