Dymanic Hyperlink based on cells value

  • Hi,

    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.

  • Re: Dymanic Hyperlink based on cells value

    You had approximately the right idea.

    This worked for me with my product codes in column A of the products Sheet, and entering the product ID in another sheet in range A7:A10

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        If Not Intersect(Target, Range("A7:A10")) Is Nothing Then
            Dim r As Range
            For Each r In Target.Cells
                r.Hyperlinks.Delete 'delete old hyperlink in case new product ID doesnt exist.
                r.Hyperlinks.Add r, "", "Products!$A$" & Application.Match(r.Value, Worksheets("Products").Range("$A:$A"), 0)
        End If
    End Sub

    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!