Locking cells in a row based on another cell's content

[WGardner]

I am trying to figure out a way in VBA to lock cells in columns A:C if the corresponding cell for that row in column D reads "Complete". So If D2 reads "Complete", A2:C2 will be locked, if D3 reads "Complete" then A3:C3 will be locked, and so on. Similarly, if D2 reads "Open" or is blank, cells A2:C2 would not be locked/ would be unlocked.

In my workbook, cells in column A can only be a date, cells in column B can only be a time, cells in column C can be any text value, and cells in column D can be "Open", "Complete", or blank.

I have seen some VBA codes to lock the entire sheet based on the value of a cell, and a code that locked a few cells in a column based on a cell, but have not been able to find anything that would work row by row like this. I have also seen a solution using data validation, but since cells A and B already have a data validation restricting to date and time I cannot get this alternative method to work.

 

[Snakehips]

@WGardner Welcome.
Can you enlighten us on......
Typically, how large is your dataset in terms of rows?
How often will the dataset be updated and need the for the locking to be revised?
How will it be updated, single row entry/editing or multi-row copy paste?

 

[WGardner]

So the actual spreadsheet goes from A to O, with O being the one that is either Open/ Complete/ blank. Rows continue to increase as we are tracking emails and calls to a hotline. Entries are made throughout the week, and I learned today that some people are entering data at night. What we are trying to do is make it so once an inquiry has been followed-up on and marked complete someone can't go back in and change any of the data in that row. Data is entered in single cells by row.

 

[WGardner]

Also, row 1 is headers.

 

[Snakehips]

All cells are Locked by default.
Firstly, you will need to run something like below to unlock columns A:O and then selectively lock on the basis of column O value

Sub Initial_Locking()
' Unlock Columns A:O
ActiveSheet.Unprotect
Columns("A:O").Locked = False
  
'Assume last row of data is determinable from column A

LR = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LR
If UCase(Range("O" & r)) = "COMPLETE" Then Range("A" & r & ":O" & r).Locked = True
Next

ActiveSheet.Protect
End Sub

Then you could have Worksheet Change Event code to automatically check for updates in column O and to lock the row as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if only single cell changed and if is column O
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.Column = 15 Then Exit Sub

'Unprotect sheet
ActiveSheet.Unprotect
'Set locking according to changed value
r = Target.Row
Range("A" & r & ":O" & r).Locked = (UCase(Target) = "COMPLETE")
'Reprotect
ActiveSheet.Protect
End Sub

Both assume that there is no Password being used.
Hope that helps.