Object defined error while sheet protected but no error when not protected

[ColinCoady]

Not sure why this is happening. The following code works fine when sheet is Unprotected but gives a Run-time error '1004' Application-defined or object-defined error when the sheet is protected. All the cells involved are unlocked. Can anyone figure out the issue & provide a solution? I don't want to leave the sheet unprotected.
Thanks
-Colin

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$E$7" Then Exit Sub
  If Target <> "Other%" Then Target.Offset(0, 2).ClearContents
  If Target <> "Other%" Then Target.Offset(0, 2).Interior.ColorIndex = 0
  If Target = "Other%" Then Target.Offset(0, 2).Select
  If Target = "Other%" Then Target.Offset(0, 2).Interior.ColorIndex = 27
  End Sub

 

[Yongle]

Unprotect and re-protect sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Address <> "$E$7" Then Exit Sub
    [COLOR=#ff0000]Me.Unprotect ("password")[/COLOR]
        If Target <> "Other%" Then Target.Offset(0, 2).ClearContents
        If Target <> "Other%" Then Target.Offset(0, 2).Interior.ColorIndex = 0
        If Target = "Other%" Then Target.Offset(0, 2).Select
        If Target = "Other%" Then Target.Offset(0, 2).Interior.ColorIndex = 27
   [COLOR=#ff0000] Me.Protect ("password")[/COLOR]
  End Sub

 

[ColinCoady]

Thank you - that worked.
Guess I was looking for something more complicated
Never thought of an easy work around.
-Colin

 

[Yongle]

Glad it resolved your problem