MS Excel: Create hyperlinks to link cells in column E to their matching value in column C in Excel 2007
Question: In 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 take a look at an example.
Download Excel spreadsheet (as demonstrated below)
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:
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, 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