Locking & Protecting Cells Affected by Macro

I have a question regarding locking.



I have the code setup as below. How ever I’m having a problem locking the cells columns L, M, N & protecting the sheet. If a user selects from the drop down list in Column J, then L/M/N will not populate and gives a debug error. Do you know how to fix this??





Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 10 Then

Select Case Target.Value



Case "Aberdeen"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Joffre"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Kegworth"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Lyalta"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Peace"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Rathwell"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Tisdale"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Virden"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case "Wilkie"

Target.Offset(0, 2).Value = "Louis Dreyfus"

Case Else

Target.Offset(0, 2).Value = ""

End Select

End If



If Target.Column = 10 Then

Select Case Target.Value



Case "Aberdeen"

Target.Offset(0, 3).Value = "PO Box 303"

Case "Joffre"

Target.Offset(0, 3).Value = "Box 9 Site 2 RR2"

Case "Kegworth"

Target.Offset(0, 3).Value = "PO Box 101"

Case "Lyalta"

Target.Offset(0, 3).Value = "GD"

Case "Peace"

Target.Offset(0, 3).Value = "PO Box 544"

Case "Rathwell"

Target.Offset(0, 3).Value = "PO Box 129"

Case "Tisdale"

Target.Offset(0, 3).Value = "PO Box 656"

Case "Virden"

Target.Offset(0, 3).Value = "PO Box 2459"

Case "Wilkie"

Target.Offset(0, 3).Value = "PO Box 689"

Case Else

Target.Offset(0, 3).Value = ""

End Select

End If





If Target.Column = 10 Then

Select Case Target.Value



Case "Aberdeen"

Target.Offset(0, 4).Value = "S0K 0A0"

Case "Joffre"

Target.Offset(0, 4).Value = "T4L 2N2"

Case "Kegworth"

Target.Offset(0, 4).Value = "S0G 1Y0"

Case "Lyalta"

Target.Offset(0, 4).Value = "T0J 1Y0"

Case "Peace"

Target.Offset(0, 4).Value = "T0H 3A0"

Case "Rathwell"

Target.Offset(0, 4).Value = "R0G 1S0"

Case "Tisdale"

Target.Offset(0, 4).Value = "S0E 1T0"

Case "Virden"

Target.Offset(0, 4).Value = "R0M 2C0"

Case "Wilkie"

Target.Offset(0, 4).Value = "S0K 4W0"

Case Else

Target.Offset(0, 4).Value = ""

End Select

End If

End Sub

I have a question regarding locking.



I have the code setup as below. How ever I’m having a problem locking the cells columns L, M, N & protecting the sheet. If a user selects from the drop down list in Column J, then L/M/N will not populate and gives a debug error. Do you know how to fix this??

Please Login or Register  to view this content.