Inserting a hyperlink via a condition

[jaypatel]

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

 

[fairwinds]

How about a formula?

=IF(H4>1000,HYPERLINK(J4),"")

 

[jaypatel]

I was hoping for a macro that would run automatically.

Jay

 

[fairwinds]

Try:

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]

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]

I dont't get that. You want hyperlink only when address is numeric??

 

[jaypatel]

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]

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.