unhide and activate worksheet based on entry in other cell

[RatherBeRidi]

I don't think this should be very difficult, but I have spent a lot of time trying different code. I can't work out how to get the value in one cell, then unhide and activate a worksheet.

Specifically - if the response in cell D95 is No, then get the value in cell C3 and unhide and activate the worksheet with the same name. The code below will unprotect and unlock cell D96 if D95 is Yes, but when No is selected in D95 the sheet is not activated and I do not receive any error message. It seems that the second If statement is ignored and it ends?

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)

If Me.Range("D95").Value = "No" Then
If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Worksheets(Target.Offset(0, 1).Value).Activate
End If

Else
ActiveSheet.Unprotect
Me.Range("D96").Locked = False
ActiveSheet.Protect
End If
End Sub
[/VBA]

[Bob Phillips]

Is it because your code says C2 and you are saying C3?

[RatherBeRidi]

I want the value that is in D2 and I thought that's what the offset was used for? However, since I could not get that to work I tried the code below. It seems to work, but I would really appreciate your advice if I need to change anything. I wasn't sure if I could include multiple If statements like this. D2 is locked and populated by a formula based on the selection of C2 using a named range. Can you also tell me how I would lock cell C2 after the user makes a selection? I know how to do it in a userform with on exit. I tried adding it to the worksheet_change, but found out that wasn't a good idea. I'm really trying to learn VBA, but also have a tight timeline. Thanks so much for your help.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("D95").Value = "No" Then
Worksheets(Me.Range("D2").Value).Visible = xlSheetVisible
Worksheets(Me.Range("D2").Value).Activate
Else
ActiveSheet.Unprotect
Me.Range("D96").Locked = False
ActiveSheet.Protect
End If
If Me.Range("D96").Value = "No" Then
Worksheets(Me.Range("D2").Value).Visible = xlSheetVisible
Worksheets(Me.Range("D2").Value).Activate
End If
End Sub
[/VBA]

[Bob Phillips]

Not sure I fully understand, but here is a first shot

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$95" Then

If Target.Value = "No" Then

Worksheets(Target.Value).Visible = xlSheetVisible
Worksheets(Target.Value).Activate
Else

Me.Unprotect
Target.Offset(1, 0).Locked = False
Me.Protect
End If

ElseIf Target.Address = "$D$96" Then

If Target.Value = "No" Then

Worksheets(Target.Value).Visible = xlSheetVisible
Worksheets(Target.Value).Activate
Else

Me.Unprotect
Me.Range("C2").Locked = False
Me.Protect
End If
End If
End Sub
[/VBA]