Inserting a Hyperlink based on a Cell Value 1

[klornpallier]

What I need to do is say "If any cell value on a worksheet = "6.2b" Then set the cell to a hyperlink of

http://www.yahoo.com".

Is this possible.

 

[SkipVought]

Hi,

Sure is.

To get the code for inserting a hyperlink, turn on your macro recorder and record inserting a hyperlink.

Then loop thru the sheet

dim rR as range
for each rR in activesheet.usedrange
  with rR
    if .value = "6.2b" then
'insert your hyperlink code here modified to range [b]rR.[/b] rather than Selection.
    end if
  end with
next

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[klornpallier]

Partly seems to work. The only trouble is if I have a cell selected on the worksheet it seems to turn that to a Link aswell. My code is:

Private Sub Worksheet_Activate()
Dim rR As Range
For Each rR In ActiveSheet.UsedRange
If .VALUE = "6.2B" Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"\\isesrfiler\NHS_HR-Pay\Data Migration\Data Management\DM Upload Team\6.2b Release Note.xls"
End If

End With
Next
end sub

 

[TonyJollans]

Hi klornpallier,

Your code as posted won't compile. can you cut and paste what you're actually running.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Loomah]

This is skip's code as (I think) you want it. What was poste has bits missing!!!!

Dim rR As Range
For Each rR In ActiveSheet.UsedRange
 If [b]rR[/b].Value = "6.2B" Then
ActiveSheet.Hyperlinks.Add Anchor:=[b]Range(rR.Address)[/b], Address:= _
        "\\isesrfiler\NHS_HR-Pay\Data Migration\Data Management\DM Upload Team\6.2b Release Note.xls"
    End If
    
Next

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[klornpallier]

Thank you Loohmah works like a dream. Forget them bits missing.

 

[SkipVought]

Loomah,

The ONLY thing missing was the hyperlink code that I suggested the chap turn on his macro recorder to "discover".

Give a man a fish, and he'll eat for a day.
Teach him how to fish, and he'll never go hungry!

Your FIRST emphasis was covered by the With...End With statement.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Loomah]

Skip
My fault, not making my post entirely clear. I wasn't referring to your post missing bits! Re-reading my post it is a bit *@%#!!

What I was referring to was the code klornpallier posted back - with the with statement missing - hence, I'd guess, Tony's comment about the code not compiling.

I only mentioned you by name to emphasise that I hadn't written the code, I was just trying to replace the bits that somehow seemed to have been lost along the way.

Appoligies if you thought I was 'having a go'!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

No prob!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}