Hyperlink Cell based on Value of another cell

Question

I Have trawled the forums looking for a solution.

I have a code for creating a hyperlink based on the column B cell value. It works but only if the I run the sub whilst selecting the cell.

What I need is for the hyperlink to get automatically added if the cell in column H's value is "ok"

Sub Hyperlinks()

Dim r As Range
Dim FilePath As String

If Intersect(Columns("B"), Selection) Is Nothing Then Exit Sub

For Each r In Intersect(Selection, Range("B2:B" & _
Cells(Rows.Count, "B").End(xlUp).Row))
If r <> vbNullString Then

FilePath = "T:\BLUEMAC\Search Paths\PDF MASTER FOLDER\"

ActiveSheet.Hyperlinks.Add Anchor:=r, _
Address:=FilePath & r.Value & ".pdf", TextToDisplay:=r.Value
End If
Next r

End Sub

Any help would be greatly appreciated.

score 1 · Accepted Answer · answered May 13 '16 at 11:35

1

Change

If Intersect(Columns("B"), Selection) Is Nothing Then Exit Sub

For Each r In Intersect(Selection, Range("B2:B" & _
Cells(Rows.Count, "B").End(xlUp).Row))

To

For Each r In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

And

ActiveSheet.Hyperlinks.Add Anchor:=r, _
Address:=FilePath & r.Value & ".pdf", TextToDisplay:=r.Value

To

If r.offset(0,6).value = "ok" then ActiveSheet.Hyperlinks.Add Anchor:=r, _
Address:=FilePath & r.Value & ".pdf", TextToDisplay:=r.Value

answered May 13 '16 at 11:35

Jochen

1,254
1
7
9

@JamesHurst Don't forget to mark as the answer if it helped (the tick on the left hand side) – SierraOscar May 13 '16 at 12:25
Jochen you have been a tremendous help, Can you maybe have a look at another problem im having? http://stackoverflow.com/questions/37219465/join-cells-based-on-value-of-a-cell-vba – James Hurst May 13 '16 at 21:20

score 0 · Answer 2 · answered May 13 '16 at 11:06

0

Like this?

Sub Hyperlinks()

Dim r As Range
Dim FilePath As String

If Intersect(Columns("B"), Selection) Is Nothing Then Exit Sub

For Each r In Intersect(Selection, Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row))
    If r <> vbNullString And LCase$(r.Offset(0, 6).value) = "ok" Then
        FilePath = "T:\BLUEMAC\Search Paths\PDF MASTER FOLDER\"
        ActiveSheet.Hyperlinks.Add Anchor:=r, _
             Address:=FilePath & r.Value & ".pdf", TextToDisplay:=r.Value
    End If
Next r

End Sub

answered May 13 '16 at 11:06

SierraOscar

17,507
6
40
68

I think James needs code that does not depend on `Selection` – Gary's Student May 13 '16 at 11:24
That is working, but it is still only working if the cell is selected and the sub run. how can i get it to go through every cell in column B automatically – James Hurst May 13 '16 at 11:24
Yes that looks like the issue, Ill need to replace the intersect part with something else, but what? – James Hurst May 13 '16 at 11:26
Jochen has the answer – James Hurst May 13 '16 at 11:50
Went out for lunch sorry - yes looks like Jochen has it – SierraOscar May 13 '16 at 12:24
Thanks for the help anyways guys, this site has been awesome – James Hurst May 13 '16 at 19:46

Hyperlink Cell based on Value of another cell

2 Answers2