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)
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 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
Advertisements