Hi Olly,
Thanks for the help so far. The edited it slightly to get the correct cells to lock and now that bit is working. I didn't think it through before but I also need to unlock the cells if the cell becomes blank or contains "S.Triage Ticket".
I have had a go but can't get the code correct, what I have is;
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculate
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If Not Intersect(Target, Range("H2:I100")) Is Nothing Then c.Value = UCase(c.Value)
If Not Intersect(Target, Range("E2:E100")) Is Nothing Then
ActiveSheet.Unprotect
If c = "" And c.Value = "S.Triage Ticket" Then c.Offset(0, 1).Resize(, 5).Locked = False
If c <> "" And c.Value <> "S.Triage Ticket" Then c.Offset(0, 1).Resize(, 5).Locked = True
ActiveSheet.Protect
End If
Next c
Application.EnableEvents = True
End Sub
When I change the cell to blank or "S.Triage Ticket", the compiler skips past that line.
The range "E2.E11" are the cells containing the drop down list. Thanks.
Bookmarks