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

Copper Contributor



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 to lock B2 through to H2.


I've gotten that far with the below code, but I need it to do that on every line for 500 rows. For example, if J5 says approved, B5 through H5 will lock as well, but any rows that don't have Approved in column J remain unlocked.



Appreciate any help on this.




2 Replies


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
        Range(Cells(lgRow, 2), Cells(lgRow, 8)).Locked = True
    End If
End Sub