Forum Discussion

Copper Contributor

Mar 21, 2023

Looking to lock and protect a number of cells based on another cell, but independent for each row

Hi, I'm quite new to VBA so apologies if this is super simple. As the title suggests, I want to lock a selection of cells based on another cell. Specifically, if J2 says "Approved", I'd like ...

Macros and VBA

Hecatonchire

Iron Contributor

Mar 21, 2023

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lgRow As Long
    lgRow = Target.Row
    If lgRow > 2 And lgRow < 1000 And Target.Column = 10 And Target.Value2 = "Approved" Then
        ActiveSheet.Unprotect
        Range(Cells(lgRow, 2), Cells(lgRow, 8)).Locked = True
        ActiveSheet.Protect
    End If
End Sub

Harry_Roberts

Copper Contributor

Mar 21, 2023

Amazing, thank you!