mercredi 3 janvier 2018

Loop inserting hyperlinks with If-condition

I basically try to put a hyperlink to a cell in Sheet1 linking it to Sheet2 if the text matches "92/65/EEC". I don't understand where I went wrong here. Could you please help?

Thank you

 Sub Legislation()

 ' Define
 Dim lrow As Long, rng As Range, cell As Range
 lrow = Cells(Cells.Rows.Count, "J").End(xlUp).Row

' Set the range where to apply the code
Set rng = Range("J5:J" & lrow)

' Define what to look for
 For Each cell In rng
 If InStr(1, cell.Value, "92/65/EEC", vbTextCompare) > 0 Then
 With cell.Validation
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'List of EU legislation '!A8", TextToDisplay:="92/65/EEC"
    End With
 End If

Next cell


 End Sub

Aucun commentaire:

Enregistrer un commentaire