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:

Please Login or Register  to view this content.

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.

Jason,

sorry...I need a little more tweaking after a little test...since the US_000 is hardcoded...I can see a problem when my number reach the next hundred. The field holder is US_xxxxxxxx (only 8 numbers after the underscore). If I enter 22702, it fills in US_00022702, but if I type 322702, it should fill in US_00322702 and not US_000322702, which is invalid. Could you help? Whatever you gave me is Greek to me.

Thanks.

This should work for you:

Please Login or Register  to view this content.

Jason

Jason,

You're my the best!
Thanks.

Glad I could help.

ok...got the code in...chugging along with my formatting and data entries...however, when I add a new column, all hyperlink functions are disabled. I've cut and paste an existng column with formula to the new ones and verified that the code is still active....but data are returning only as a text. How do I "refresh" it to accomodate new columns?

Thanks.