To find if a value exists in another sheet and provide a hyperlink. Below is the code I have used.
Sheets("Sheets1").Select
Excel.ActiveSheet.Range("F" & i).Select
conditionDescription = Excel.ActiveSheet.Range("F" & i).Value
conditioncelladdress = ActiveCell.Address
Sheets("Sheets2").Select
Set RangeObj = Cells.Find(conditionDescription, After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If RangeObj Is Nothing Then
conditionCell = False
'Exit For
Else: RangeObj.Select
celladdress = ActiveCell.Address
Sheets("Sheets1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"(Sheets2)!celladdress", TextToDisplay:=conditionDescription
End If