hyperlink

[LadyHarper]

I have a spreadsheet with over 2000 comments in it. I have used VBA coding to create a new sheet that has all the comments on it. I now want to make it so when I click on a cell in the main spreadsheet that has a comment in it, it takes me to the matching cell number listed in the column that states all the cell's with comments in them.

 

[NickRed18]

I'm terribly sorry about the trouble... I promise I test my code before I reply, so I'm not sure why it's not working anymore.
I think I fixed it. Once again, I apologize. Where it says [Main Sheet Name], put in the name of your main sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("Sheet1").Activate
    If Worksheets("Sheet1").Range("D2:D2886").Find(Target) Is Nothing Or IsEmpty(Target) = True Then
        On Error Resume Next
        Worksheets("[Main Sheet Name]").Activate
    Else
        On Error Resume Next
        Worksheets("Sheet1").Cells(Application.WorksheetFunction.Match(Target, Worksheets("Sheet1").Range("D2:D2886"), 0) + 1, 4).Select
                                                                                              'The +1 to account for a header
    End If
End Sub

 

[LadyHarper]

I am not sure what I am doing wrong. When I click in cell E15 that has a comment in it, it brings up Sheet1 but is not going to E15 in the B column. I correct the VBA where you have D to B... Still not working

 

[LadyHarper]

It is also jumping to Sheet1 from a box without a comment in it

 

[V_Malkoti]

Try this:

In each sheet which needs click functionality (Main, Secondary, Other, etc), paste following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    SearchCommentCell Target
End Sub

Create a new module in your VBA window (Insert -> Module) and paste following code in it:

Public Sub SearchCommentCell(Target As Range)
    Dim srchSht As Worksheet
    Dim srchRng As Range, foundCell As Range
    Dim firstAddress As String
    
    'Sheet to search
    Set srchSht = ThisWorkbook.Sheets("Sheet1")
    'Range to search
    Set srchRng = srchSht.Range("B:B")
    'Find the cell - assuming addresses are written as A5 and not A$5$ in Sheet1
    Set foundCell = srchRng.Find(What:=Replace(Target.Address, "$", ""))
    If foundCell Is Nothing Then
        ' not found
    Else
        ' cell found
        ' note down the address of first cell found
        firstAddress = foundCell.Address
        Do
            ' check if the sheet name matches, ie, is this comment for Main!A5 or Secondary!A5
            If (foundCell.Offset(0, -1).Value = ActiveSheet.Name) Then
                ' sheet name matched; activate found cell
                ThisWorkbook.Sheets("Sheet1").Activate
                foundCell.Select
                Exit Do
            End If
            ' else search for next cell with the address
            Set foundCell = srchRng.FindNext(After:=foundCell)
        ' loop until search wraps around
        Loop Until foundCell.Address = firstAddress
    End If
End Sub

 

[LadyHarper]

Thank you so much it worked...