I have a specific table with the master data of the products. The table contains product ID and description. In another sheet the user enter random product IDs. For each product ID he enters I want to automatically convert the cell to hyperlink to the correct cell of the product table.
I use the Worksheet_Change function to identify the range of cells that a user changes but with no success.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A7:A10")) Is Nothing Then Dim r As Range For Each r In Target.Cells Range(r.Address).Value = _ "=HYPERLINK(""#Products!""&ADDRESS(MATCH(" & r.Address & ";Products!$A:$A;0);1);" & r.Address & ")" Next End If End Sub