conditioning formatting?

Hi there!

I have a question about conditioning formatting that I hope you're able to help.

I wanted to set a range of cells to automatically create a hyperlink based on the data typed into it. for example, if I type '20300' into cell A1, I would like the display text to change from '20300' to US_00020300 and have it hyperlink to 'http://scr.blahblahblah.com/CHRDetails.pl?CHRID=US_00020300'.

Is this doable? Please help.
Thanks in advance!

Try this. Right-click on the sheet tab, select View Code, and copy this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim Rng As Range
    Dim strHyp As String
    
    Set Rng = Range("A1:A100")
    strHyp = "http://scr.blahblahblah.com/CHRDetails.pl?CHRID="
    
    If Not Intersect(Target, Rng) Is Nothing Then
        
        If Not IsEmpty(Target) Then
            Application.EnableEvents = False
            Target = "US_000" & Target
            ActiveSheet.Hyperlinks.Add Target, strHyp & Target
            Application.EnableEvents = True
        End If
        
    End If
    
End Sub

You may want to change the above noted range (this is set so that it will only create a hyperlink if it is in range A1:A100).

You will also, obviously, need to change the web address if it is not accurate.

HTH

Jason

Hi Jason,

Thanks for your prompt reply. Ok. I've cut and pasted it in, made the few necessary corrections then closed and returned to excel. Should I compile it or something because nothing is happening when I typed '22702'.


thanks.

In which cell did you type the number?

Hi Jason,

I typed it in A1, A4...it returned just the number I typed it.
Oh wait.....I cut and paste and didn't see the very first line till now! hold on....
ok. that worked. THANKS!!!!

This is the best site ever! I've posted here just once before and have gotten very prompt and accurate replies. Thanks for your assistance!

LOL Glad it worked for you.