Mar 21 2023 09:11 AM
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,
Mar 21 2023 09:38 AM
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
Mar 21 2023 09:43 AM