VBA to lock cells in different ranges

[NeilBarn1701]

Hi,

I am building timesheets for the company staff to complete their hours and jobs, see below.

The sheet has Monday to the Weekend across in columns with room for estimate hours, actual hours, the task and job number.
The next week with the same columns is underneath etc.

To the side each weekly block has a totalizer, request for overtime and the part I am struggling with management approval and sign off for the work done this week (it is in merged cell AI20 for Week 31)

What I have been tasked to do is each of these weeks when the line manager changes the word 'Manager' to his initials, it locks that weeks work so the hours cannot be altered unless with the protected password. The same thing is then required for the end of the next week, and then the week after that etc.

I have this code so far that @FormR member prepared for me:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AI19")) Is Nothing Then
Me.Unprotect
If Range("AI19") = "Manager" Then
Range("A4:AC17").Locked = False
ElseIf Range("AI19") = "NB" Then
Range("A4:AC17").Locked = True
End If
Me.Protect
End If
End Sub


Thee issue that I have is the code only looks at that one single week. If I try and replicate the code to include the next week in a separate If statement e.g. Range A25:AC38 then I get errors, any help would be appreciated.

Thanks

Neil

 

[rollis13]

This could be a solution for you as long as the structure of your weekly blocks is always the same: cells with validation (Manager/NB) in the column AI will always be 21 rows apart starting from row 19 (as per your attached example).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AI:AI")) Is Nothing And (Target.Row + 2) Mod 21 = 0 Then
        Me.Unprotect
        If Target = "Manager" Then
            Range(Cells(Target.Row - 15, "A"), Cells(Target.Row - 2, "AC")).Locked = False
        ElseIf Target = "NB" Then
            Range(Cells(Target.Row - 15, "A"), Cells(Target.Row - 2, "AC")).Locked = True
        End If
        Me.Protect
    End If
End Sub

PS. tested on Windows.

 

[NeilBarn1701]

Thank you very much, this is what I am looking for, much appreciated.

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.