Forum Discussion

Harry_Roberts's avatar
Harry_Roberts
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 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.

 

Thanks,

 

Macros and VBA
  • Hecatonchire's avatar
    Hecatonchire
    Iron Contributor
    Mar 21, 2023

    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 

Resources