Link to home
Start Free TrialLog in
Avatar of mmcompact
mmcompactFlag for United States of America

asked on

Convert selected cells to web address link

In a sheet, there are cells contain something link "mydomain.com", "mydomain.info", "mydomain.org"...
Once I select them, I want to run a VBA that will convert the text to hyperlinks "http://www.mydomain.com", but keep its original formatting and without showing "http://www."
Avatar of rspahitz
rspahitz
Flag of United States of America image

Try this:


Sub AddHyperlink()
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://www." & ActiveCell.Value, TextToDisplay:=ActiveCell.Value
End Sub
Without using VBA, you can right click the cell and then Insert a hyperlink.

In your example, mydomain.org you can type in the hyperlink address box as www.mydomain.org

Sincerely,
Ed
If you want to select more than one and apply it, try this instead:


Sub AddHyperlink()
    Dim objCell As Range
    For Each objCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=objCell, Address:= _
            "http://www." & objCell.Value, TextToDisplay:=objCell.Value
    Next
End Sub
Avatar of mmcompact

ASKER

rspahitz:

your solution works, but it changed the text formatting to system default. Can I keep the original formatting, like font, font size, no underline etc...
ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
cool, thanks