How to create a Hyperlink based on Cell Contents

chatguy · October 15th, 2007, 02:35 PM

Hi all,

I've tried searching the forums for an answer, but had no luck. Will try my luck posting...and maybe it'll also help someone else as well:

-> In Excel Cells L4 to L9999, I would like to have Excel have that cell Hyperlinked of a Google search of the contents which were entered in that cell. (or, if that same cell can't be Hyperlinked, then a neighboring cell is OK)

-> So, for example, if cell L4 contained the word: "testing",
I would like to be able to click on that text in the cell to open a browser and take me to: "http://www.google.com/search?q=testing"

...I hope this can be done without writing a macro, (maybe by a formula instead?) BUT, if it's the only way of doing it, then yes, please, in that case, I would LOVE to know how to write/enter the macro.

Thanks SO much for your help!!
CG

chatguy · October 15th, 2007, 03:25 PM

Update: I'm sorry... I realized this needs to be done via a macro, NOT a formula.

Would anyone know how to do this? ...I'm hoping for some kind of macro that will convert the text to a link (preferably converts on-the-fly, but if not, that's OK)

Thanks again. -CG

chatguy · October 15th, 2007, 05:18 PM

FYI - I'm still hoping for an answer :(, if anyone happens to know. Thank you!

I have also posted this question on another web site (which I have later learned is called "Crossposting" and I PROMISE to post the answer here if I get the answer on another web site--I did not mean to break any rules, and will right the wrong as soon as I get the answer). In the interest to not break any NDA rules, if I get an answer, I will not post the name of the web site that I got the answer from, but I will post the answer here.

chatguy · October 15th, 2007, 09:39 PM

The following is the answer for anyone else that may also need help with the code. Thanks for the help, Reafidy! -cg

Reafidy's code is as follows:

Code:

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As  Range) 
    If Target.Column = 12 And Not Target.Value = "" Then 
        Application.EnableEvents = False 
        Hyperlinks.Add Target, Address:="http://www.google.com/search?q=" & Target.Value, TextToDisplay:="Click Here" 
        Application.EnableEvents = True 
    End If 
End Sub

Shasur · October 15th, 2007, 09:43 PM

Here you have CG
Sub Create_HyperLinks_To_Google()

Dim i1 As Integer
Dim sA As String

For i1 = 4 To Range("L:L").Cells.SpecialCells(xlCellTypeLastCell ).Row
sA = Trim(Range("L" & CStr(i1)).Text)
ActiveCell.Hyperlinks.Add Range("L" & CStr(i1)), "http://www.google.com/search?q=" & sA & ""
Next i1

End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)