Dynamic links creation

TomasCiganek 

It seems like you do not need a hyperlink in A1. After all, clicking on the cell to select a different fruit would trigger the existing hyperlink instead, right? You could generate a hyperlink in a separate cell. But that's a second action for the user to get there. Instead…

Build the validation for cell A1 as usual, for example, as described in Excel Drop Down Lists - Data Validation. Within a Worksheet_Change event handler for Sheet1, when cell A1 changes, do a lookup to find the address on Sheet2 for the selected fruit, and execute the Application.Goto method for that address.

Here is possible code for the event handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngFruitType    As Range
    Dim rngChgdCellsOfInterest  As Range
    Dim strFruitType    As String
    '
    Dim objSheet2   As Worksheet
    Dim in4Row  As Long
    
    '----   If a different type of fruit was selected...
    Set rngFruitType = Range("A1")
    '
    Set rngChgdCellsOfInterest = Intersect(Target, rngFruitType)
    If rngChgdCellsOfInterest Is Nothing Then
        GoTo WkshtChg_Exit
    End If
    '  --   ...
    strFruitType = rngFruitType.Value
    If strFruitType = "" Then
        '...the changed cell(s) did not include the Fruit Type cell.
        GoTo WkshtChg_Exit
    Else
        '  --   Find the matching row on Sheet2.
        Set objSheet2 = Sheets("Sheet2")
        in4Row = WorksheetFunction.Match(strFruitType _
                , objSheet2.Range("A1:A99"))
        '  --
        Application.Goto "Sheet2!R" & in4Row & "C1"
    End If
    
WkshtChg_Exit:
    Exit Sub
End Sub