VBA to lock adjacent cells if not blank

[lander2]

Hello

An easy one I'm sure but I have not found a solution to this anywhere.

I've made a holiday tracker for staff to add their own leave, I've protected cells and added buttons including one the manager can click to enter the password and unlock for authorisation. Once holiday on a particular row has been authorised by the manager entering their name against that row, say in column G, I need the cell property for B to G on that row to be switched to locked. The protection gets turned back on when saved already so it's just locking those cells I can't do.

I've found the following code but not sure how to modify to include the full range e.g. B18:G50.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value <> "" Then
    ActiveSheet.Protect Contents:=False
Range(Target.Address).Locked = True
    ActiveSheet.Protect Contents:=True
End If
End Sub

Hope that makes sense and thanks for looking.

 

[lander2]

I

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 7 Then
      If Target.Value <> "" Then
         Intersect(Target.EntireRow, Range("B:G")).Locked = True
      Else
         Intersect(Target.EntireRow, Range("B:C,E:F")).Locked = False
      End If
   End If
End Sub

That is awesome. Thank you for your help with this. I did manage to get it working by tinkering with the previous code but this is so much better so used this instead. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback