Protecting cells

Hi Maia,

I hope the following will help:

Copy the code below, right-click a sheet tab, select View Code, then paste the code below into the code window which opens. Create a drop-down in cell B3 with Promote and Demote as options. The code below will lock cells C3 to C10 if you select Promote, while nothing will happen if you select Demote. The password to unprotect is PASSWORD. You can change bits of the code to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[c3:c10].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if B3's value is anything else or is deleted.
ElseIf [B3] = "Demotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[c3:c10].Locked = False
'Optional, reprotect sheet
'ActiveSheet.Protect ("PASSWORD")
End If
End Sub

Kind regards
Marius Barnard
Excel Trainer