Lock a Row of Cells After Another Cell has Been Updated & Now Contains a Data Value

[TuskenRaider52]

Hi all--

So essentially I want to make it so a row will lock after a particular cell has data added to it.

Example -- Lets say I've filled out cells A2, B2 & C2. And cell D2 is my confirmation those have been completed. Once I type 'Yes' into D2, I want the sheet to automatically lock cells A2, B2 & C2. The logic being that once the review is completed, these cells can't accidentally be changed later. If the data in D2 is later deleted, then A2, B2 & C2 can would again be editable.

It is also worth noting that Data Validation is already being utilized in these cells. I'm guessing a macro will be needed? Also, it would be great if the row changed color after locked/unlocked by updating D2 but this is less of a concern since this can probably be accomplished with some conditional formatting.

Is there a way locking these cells after D2 is updated?

Any help would be great, thanks--

[jomili]

Yes, but there's some considerations:
You say "make it so a row will lock after a particular cell has data added to it. " "Once I type 'Yes' into D2". What if you type "No"? Lock, or don't lock?
To Lock we would apply protection to the worksheet, so whatever cells you want to be locked would be locked. You say "If the data in D2 is later deleted, then A2, B2 & C2 can would again be editable." So, do you mean lock it so nobody can change D2, unless they want to delete it and change it? Not sure that makes sense.
Changing color is easy either through VBA or with CF.

[TuskenRaider52]

Hi Jomili -- thanks for replying.

Yes, but there's some considerations:
You say "make it so a row will lock after a particular cell has data added to it. " "Once I type 'Yes' into D2". What if you type "No"? Lock, or don't lock?

‘Yes’ was just an example. Could also be ‘No’, ‘N/A’, etc. Regardless of what is entered into D2, I want the entire row (A-C) to lock. And when I say lock, I mean it as in you can no longer change or edit those cells.

Also, if the entered data in D2 is removed, then the row (A-C) would ‘unlock’ and the cells could then be edited again.

To Lock we would apply protection to the worksheet, so whatever cells you want to be locked would be locked.

I'm already using the Protect Sheet function on other cells in the same sheet to lock those cells as well. I also do not think this funciton would work in this case. The Protect Sheet function will lock certain cells by an Admin. For what I want to do here, I want the user to be able to lock the row once they mark it as reviewed/completed in D2 and then also be able to remove that data point in D2 in case they want to change something later. It’s more of a self-imposed safeguard by the user(s) to ensure previously entered data is not accidently changed. This will be a shared doc. I want them to be able to easily flip back and forth if needed.

You say "If the data in D2 is later deleted, then A2, B2 & C2 can would again be editable." So, do you mean lock it so nobody can change D2, unless they want to delete it and change it? Not sure that makes sense.

No, I mean once D2 has info, it locks A2, B2 & C2. If you remove D2, then A2, B2, & C2 can be edited again. D2 should never be locked. But once any value is added to it, it locks all previous cells until it is removed.

Changing color is easy either through VBA or with CF.

Cool, that’s what I suspected.

[TuskenRaider52]

Are you able to help?

[Joske920]

Hi TuskenRaider52,

First you need to unlock the columns you are going to use and adapt the code to that
this code is for A B C D like you suggested

this code needs to go in the worksheet you are using it on

Please Login or Register  to view this content.

hope this helps you out

[jomili]

TuskenRaider52,
Let us know if Joske920's code did the trick for you, or if you need more help. BTW, below I've added code to change the cell interior color to green (you can choose any colors; color index numbers can be found at https://www.automateexcel.com/excel-...r-color-index/)

Please Login or Register  to view this content.