If cell contain specific text then open a specific sheet

Question

today I've ask if someone can help me , but I think I didn't put the correct situation .

I will describe now deeply so everybody can understand and help me please. please see first the picture.

On each horizontal line , if the cell "E" contain word like Design the link in cell "J" will open the sheet specific for design . if cell "E" contain random name ,then cell "J" should open another sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim myRange As Range
     Dim myCell As Range
     If Not Intersect(Target, Range("J4:J153")) Is Nothing Then
     If Target.Value <> "" Then
       employee = Target.Offset(0, -7).Value
           Set myRange = Range("E4:E150")
           For Each myCell In myRange
           If myCell Like "*Design*" Or _
               myCell Like "*Junior*" Or _
               myCell Like "*Fresh*" Or _
               myCell Like "*designer*" Then
        Sheets("Employee Review Design").Visible = True
        Sheets("Employee Review Design").unprotect "1234"
        Sheets("Employee Review Design").Range("C3").Value = employee
        Sheets("Employee Review Design").Activate
        Sheets("Employee Review Design").protect "1234"
      Else
        Sheets("Employee Review").Visible = True
        Sheets("Employee Review").unprotect "1234"
        Sheets("Employee Review").Range("C3").Value = employee
        Sheets("Employee Review").Activate
        Sheets("Employee Review").protect "1234"
    End If
    Next myCell
    End If
If Not Intersect(Target, Range("D4:D153")) Is Nothing Then
    Set fnameRange = Range("$D$4:" & Range("D4").End(xlDown).Address)
    ThisWorkbook.Names.Add Name:="fullnames", RefersTo:=fnameRange
End If
End If
End Sub

Answer 1

• Updating the named range (fullnames) doesn't make any sense if users didn't change Col D.

Split it into two events Sub:

• Worksheet_SelectionChange : open the sheet when users select a cell on Col J
• Worksheet_Change : update named range reference of fullnames only when users change a cell on Col D

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range
    Dim myCell As String, sSht As String
    With Target
        If .CountLarge = 1 Then
            If .Row > 3 And .Column = 10 And Len(.Value) > 0 Then
                myCell = Me.Cells(.Row, "E")
                If myCell Like "*Design*" Or myCell Like "*Junior*" Or myCell Like "*Fresh*" Or myCell Like "*designer*" Then
                    sSht = "Employee Review Design"
                Else
                    sSht = "Employee Review"
                End If
                Application.EnableEvents = False
                With Sheets(sSht)
                    .Visible = True
                    .Unprotect "1234"
                    .Range("C3").Value = Me.Cells(Target.Row, "C").Value
                    .Activate
                    .Protect "1234"
                End With
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oName As Name, fnameRange As Range
    With Target
        If .CountLarge = 1 Then
            If .Row > 3 And .Column = 4 Then
                On Error Resume Next
                Set oName = ThisWorkbook.Names("fullnames")
                On Error GoTo 0
                Set fnameRange = Me.Range("D4", Me.Cells(Me.Rows.Count, "D").End(xlUp))
                If oName Is Nothing Then
                    ThisWorkbook.Names.Add Name:="fullnames", RefersTo:=fnameRange
                Else
                    If Mid(Replace(oName.RefersTo, Chr(34), ""), 2) <> _
                        Split(fnameRange.Address(True, True, , True), "]")(1) Then
                        oName.Delete
                        ThisWorkbook.Names.Add Name:="fullnames", RefersTo:=fnameRange
                    End If
                End If
            End If
        End If
    End With
End Sub