Macro to create hyperlinks within same spreadsheet

[QueenS]

Hi..I'm just discovering the wonders of macros. I have the minimum-est possible programming knowledge. I would greatly appreciate a macro code to tackle this cumbersome task:

• Say B1:B10 contains strings 1A-10A in its counting order. (My spreadsheet has 100-1000 of these)
• Column A in the same spreadsheet contains the same strings

I want a code that will place hyperlinks in the cell containing the number so that activating the hyperlink in either column will select same number in the other column.

i.e. Selecting B1 that contains 1A will take me to cell location in Column A that contains 1A and vice versa.

Help please..!

 

[Yard]

Try this

Sub AddHL()
 
Dim WS As Worksheet, rngSource As Range, rngLook As Range, rngLast As Range, rngC As Range, rngFind As Range
 
Set WS = Worksheets("Sheet1")
Set rngSource = WS.Range("B1:B10")
Set rngLook = WS.Range("A1:A1000")
Set rngLast = rngLook.Cells(rngLook.Cells.Count)
 
rngSource.Hyperlinks.Delete
rngLook.Hyperlinks.Delete
 
For Each rngC In rngSource.Cells
 
    Set rngFind = Nothing
    Set rngFind = rngLook.Find(rngC.Value, After:=rngLast, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
 
    If Not rngFind Is Nothing Then
 
        WS.Hyperlinks.Add Anchor:=rngC, Address:="", SubAddress:=rngFind.Address(0, 0), ScreenTip:="Click here"
        WS.Hyperlinks.Add Anchor:=rngFind, Address:="", SubAddress:=rngC.Address(0, 0), ScreenTip:="Click here"
 
    End If
 
Next rngC
 
 
End Sub

 

[QueenS]

Morning Yard...

Thank you for the prompt reply..Appreciate it.. ur code worked magic...