Hyperlink to Cell on another sheet

[Eldrod]

This one has been kicking my rear all morning. I have a macro that produces a list of certain cell references and the text content of that cell on a log sheet. What I want to do is have that cell reference in column A which looks like "Sheet2!B3" display just like that but also be a hyperlink to that address.
The following produces what I expect to see, but when I click on the hyperlink, I get error "The address of this site is not valid.". Can anyone see what I'm doing wrong here? Thanks!

Function LogEntry(Cell_Reference As String, Log_Text As String)
On Error Resume Next
Text_Row = FindNextOpenTextCell(Text_Sheet_Name)

'Worksheets(Text_Sheet_Name).Range("A" + CStr(Text_Row)) = "=HYPERLINK(" & Cell_Reference & ", " & "link ref)"
'Set MyLink = Worksheets(Text_Sheet_Name).Hyperlinks.Add(
Worksheets(Text_Sheet_Name).Hyperlinks.Add _
    Anchor:=Cells(CStr(Text_Row), "A"), _
    Address:=Range(Cell_Reference).Value, _
    TextToDisplay:=Cell_Reference
    
Worksheets(Text_Sheet_Name).Range("B" + CStr(Text_Row)) = Log_Text
End Function

The commented out lines were some of the other things I was trying...

 

[Eldrod]

I found my own resolution. When in doubt, use the Macro recorder. The problem was the missing SubAddress parameter. There are a lot of hyperlink examples out there and most of them leave off the SubAddress. Hopefully, this will save someone else a lot of time.

Function LogEntry(Cell_Reference As String, Log_Text As String)
Text_Row = FindNextOpenTextCell(Text_Sheet_Name)
Worksheets(Text_Sheet_Name).Hyperlinks.Add _
    Anchor:=Cells(CStr(Text_Row), "A"), _
    Address:="", _
    SubAddress:=Cell_Reference, _
    TextToDisplay:=Cell_Reference
    
Worksheets(Text_Sheet_Name).Range("B" + CStr(Text_Row)) = Log_Text
End Function