Inserting a hyperlink via a condition

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
Hi,

How would you write a macro that would insert a hyperlink in column L via a condition where:

- any cells in column h(x) is >1000

and where the the actual link is ""columnJ(x)"" where the text in column J is blue (a hyperlink)....

an example

cell h2 = 1003, cell j2= text then in cell L2 insert hyperlink called "text.xls"
cell h3 = 993, cell j3 = text1 then insert no hyperlink.

Jay
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Try:

Code:
Sub xxx()
Dim ce As Range
For Each ce In Range("H:H")
If ce.Value > 1000 Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(ce.Row, 12), Address:=Cells(ce.Row, 10)
Next ce
End Sub
 

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385

ADVERTISEMENT

Hi this works a treat..... but realised one problem....

i also want the hyperlink to be true where column J is only a numerical field and not a text field.

Cheers

Jay
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
I dont't get that. You want hyperlink only when address is numeric??
 

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
yes......

for example,
if j2="alpha" and h=1400 then do not create hyperlink

or if j3="145" (ie a number) and h=1401 then create a hyperlink.

ie still have the condition where h column must also be greater than 1000 as well.

Jay
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Code:
Sub xxx()
Dim ce As Range
For Each ce In Range("H:H")
If ce.Value > 1000 Then If WorksheetFunction.IsNumber(Cells(ce.Row, 10)) Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(ce.Row, 12), Address:=Cells(ce.Row, 10)
Next ce
End Sub

But I still don't get why you want number as link.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,091
Messages
5,768,039
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top