Unlocking Range in a protected Sheet - VBA

Hey,
I've been trying to unlock a specific range in a protected sheet after performing the below steps, but it doesn't seem to be working.
What I am actually trying to do here is to unlock a specific range ( =$X$3:$Y$5000,$AA$3:$AD$5000,$AN$3:$AO$5000,$G$3:$V$5000,$A$3:$D$5000 ; (Namely: "Merch") ).
But the codes I tried with just unlock a cell, and it gets permanently unlocked, and the other cells under "Merch" remain locked.
Is there anyway to unlock this range?


Note: Sheet is protected, and the range "Merch" is unlocked by the users before the below code gets triggered.

Thanks in Advance!

Please Login or Register  to view this content.

Hello Tony,
Thank you so much for your response.

This is what I got..



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As String
Dim myFoundRange As String
Dim wks As Worksheet
Dim rng As Range

If Target.Column = 1 And Target.Count = 1 Then
If Len(Target.Value) > 0 Then
Worksheets("MASTERFILE").Unprotect Password:="ssmaster16"
Application.EnableEvents = False
myRange = Target.Address
On Error GoTo exit_sub
Range(Cells(1, "A"), Cells(Target.Row - 1, "A")).Find(What:=Target.Value, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
myFoundRange = ActiveCell.Address
Range(myFoundRange).Resize(, 16).Copy
Range(myRange).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set wks = ActiveSheet
Set rng = wks.Range("merch")
rng.Locked = False
ActiveSheet.Protect Password:="ssmaster16"


End If
End If
exit_sub:
Err.Clear
Application.EnableEvents = True


End Sub



Now I am getting RUN TIME ERROR'1004': " Method "Range of objects" _ "Worksheet" Failed "


Feel free to laugh at me