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

Copper Contributor

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 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.

 

Harry_Roberts_0-1679414455153.png

Appreciate any help on this.

 

Thanks,

 

2 Replies

Hi

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