Lock rows in a protected sheet based on value of a column of the same row.

[Haellion]

Hi, I have a sheet protected by password but with the ranges C19:G23 and C32:L70 unlocked, allowing the edition of the ranges only. The code below verifies if there is data on column C and puts an "Ok" in column B of every row where data is found in column C. Now I want to incorporate in the code a section to lock for edition only the rows where the "Ok" is found in column B within the ranges C19:G23 and C32:L70. Can anyone help me please?

Sub Test()

ActiveSheet.Unprotect Password:="Maze"

Dim mainworkBook As Workbook

Set mainworkBook = ActiveWorkbook

Application.ScreenUpdating = False

Dim lastRow As Long

Dim cell As Range

lastRow = Range("C" & Rows.Count).End(xlUp).Row

For Each cell In Range("C32:C70" & lastRow)

If InStr(1, cell.Value, "") <> 0 Then

cell.Offset(, -1).Value = "Ok"

End If

Next

Application.ScreenUpdating = True

ActiveSheet.Protect Password:="Maze"

End Sub

 

[Marc L]

Hi !

At very beginner level, just using the Macro Recorder …

According to your initial code (if it already works !) :​

Sub Test()
    Const PSW = "Maze"
    Dim Rc As Range
    ActiveSheet.Unprotect PSW
    Application.ScreenUpdating = False
For Each Rc In Range("C32", [C71].End(xlUp))
      If Rc.Value > "" Then
         Rc.Offset(, -1).Value = "Ok"
         Rc.EntireRow.Locked = True
      End If
Next
    Application.ScreenUpdating = True
    ActiveSheet.Protect PSW
End Sub

Do you like it ? So thanks to click on bottom right Like !
​