totn Excel

MS Excel 2007: Create hyperlinks to link cells in column E to their matching value in column C

This Excel tutorial explains how to write a macro to create hyperlinks to link cells in one column to their matching value in another column in Excel 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2007, I was wondering if there was somehow to link 2 cells based on their values being equal? So if I clicked on cell E9 (which has a value of 41), it would take me to cell C2 (which also has the value of 41) , based on the data below:

A B C D E
1 Apples 37 TEST10 36
2 Bananas 41 TEST70 36
3 Oranges 42 TEST71 36
4 Tangarines 43 TEST72 36
5 Grapes 146 TEST5C 36
6 Pineapple 38 TEST20 37
7 Pears 39 TEST30 38
8 Strawberries 40 TEST40 39
9 Blueberries 44 TEST73 41
10 Peaches 45 TEST74 42

I need to do this for each cell in column E, so that when I click on the value in column E, it will take me to the matching value in column C.

Answer: Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

In our spreadsheet, we've set up column E with values that we wish to hyperlink to the matching value in column C. On this sheet, we've created a button that when clicked will launch a macro. This macro will create the hyperlinks in column E.

In our example, we've clicked on the button. Now the hyperlinks are created as follows:

Microsoft Excel

In this example, cell E6 will hyperlink to C1, E7 will hyperlink to C6, E8 will hyperlink to C7, and so on.

You can press Alt+F11 to view the VBA code.

Macro Code

The macro code looks like this:

Sub Update_Hyperlinks()

   Dim LRowE As Integer
   Dim LRowC As Integer
   Dim LContinue As Boolean

   'Clear all hyperlinks from the active sheet
   ActiveSheet.Hyperlinks.Delete

   'Start at row 1 when creating hyperlinks for column E
   LRowE = 1

   'Create hyperlinks in column E until a blank value is encountered in column E
   While Len(Range("E" & CStr(LRowE)).Value) > 0

      'Start at row 1 (when searching column C values)
      LRowC = 1
      LContinue = True

      'Stop searching column C when either a match is found, or
      ' a blank value in column C is found
      While LContinue = True

         'Found a match between column E and column C, set hyperlink and
         ' set boolean variable to not search any more for a match
         If Range("E" & CStr(LRowE)).Value = Range("C" & CStr(LRowC)).Value Then

            'Select the location for the new hyperlink
            Range("E" & CStr(LRowE)).Select

            'Add the hyperlink to the column C value
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
            Address:="", _
            SubAddress:="C" & CStr(LRowC), _
            ScreenTip:="C" & CStr(LRowC)

            'Found a match, so do not continue
            LContinue = False

         End If

         'Move to next row in column C
         LRowC = LRowC + 1

         'A blank value is found in column C, do not continue further
         If Len(Range("C" & CStr(LRowC)).Value) = 0 Then
            LContinue = False
         End If

      Wend

      'Move to next row in column E
      LRowE = LRowE + 1

   Wend

End Sub