Private Sub Worksheet_Change(ByVal Target As Range)
Lien Intersect(Target, Columns("B:F"), Me.UsedRange)
End Sub
Private Sub CommandButton1_Click()
Lien Intersect(Columns("B:F"), Me.UsedRange)
End Sub
Sub Lien(plage As Range)
If plage Is Nothing Then Exit Sub
Dim cel As Range, lig
With Sheets("Sheet2")
For Each cel In plage
If cel <> "" Then
lig = Application.Match(cel, .Columns(1), 0)
If IsNumeric(lig) Then Me.Hyperlinks.Add Anchor:=cel, Address:="", _
SubAddress:="Sheet2!" & .Cells(lig, 1).Address, TextToDisplay:=cel.Text
End If
Next
End With
End Sub