Loop through the results of Find / Add Hyperlink to all cells containing certain text

Attach a small sample workbook including desired results, and somebody will help.

Hi,

So, here's the sample workbook.

As you can see, there is a sheet called "XYZ" and throughout the workbook there are cells whose entire contents are "XYZ". Everywhere I have such a cell, I would like a link placed to the sheet "XYZ" (like in Sheet1!B2)

The contraints are:
- I cannot use the formula "=Hyperlink(x,y)" in the cell and
- I would prefer not to have to loop through all cells in all sheets to find occurences of "XYZ" as the real workbook is huge and there I would have to loop through the entire workbook 40+ times to look for 40 different worksheet names.

I hope I've managed to explain this a bit better. Make sure to let me know if you want any more information.

Thanks again,

js

This does XYZ, but if you multiple sheets, I think another loop would have to be built in to the code.

Sub x()
 
Dim i As Long, rFind As Range, sFind As String, ws As Worksheet

sFind = "XYZ"

For Each ws In Worksheets
    With ws.Columns(2)
        Set rFind = .Cells(1, 1)
        For i = 1 To WorksheetFunction.CountIf(.Cells, sFind)
            Set rFind = .Find(What:=sFind, After:=rFind, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                ws.Hyperlinks.Add Anchor:=rFind, Address:="", SubAddress:=sFind & "!A1", TextToDisplay:=sFind
            End If
        Next i
    End With
Next ws
        
End Sub

That's exacly what I was looking for.

Excellent reply.

Thanks very much!