Need an Excel macro to highlight cells containing specific text triggered by a hyperlink

[stressler]

I have a hyperlink on one Excel sheet that takes me to data on another sheet based on matching the text in a certain cell. My sheet contains a hyperlink in each row. Each hyperlink I click takes me to another sheet based on the text from column in that row. Here's my hyperlink formula...
=HYPERLINK("#'Report'!"&ADDRESS(MATCH(A2,Report!A:A,0),3),"View")

The hyperlink matches the data in column A of my row with the first row containing that same text on my "Report" tab and takes me to that row.

What I'm looking for is a macro that is triggered when I click on the hyperlink to highlight all rows containing that same text (the text from column a of that hyperlink row) on the "Report" tab. So basically, I want to click the hyperlink on my "Summary" tab, it will take me to the first row on the "Report" tab containing the same text found in column A current row on the "Summary" tab. Then at the same time as the hyperlink takes me to the "Report" tab to find my specified text row, I also want it to kick off a macro to highlight all rows containing this same specific text a different color.

Is this possible?

 

[Roderick_E]

This is a SAMPLE with Sheet1 containing the hyperlinks in column A and the data it will link to in Sheet2 column A.
This code is in Sheet1, right click the tab and VIEW CODE, insert this code. Modify as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
lastrow = Sheet2.Columns(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Sheet2.Range("A:A").Interior.Color = xlNone 'reset colors on destination sheet
For x = 1 To lastrow
If Sheet2.Cells(x, 1) = Target Then
Sheet2.Cells(x, 1).Interior.Color = vbYellow
End If
Next x
End If
End Sub

 

[stressler]

Okay, I've had to modify your macro to fit my structure. here's what I updated it to. It isn't working, but perhaps I modified something incorrectly, Here's what I updated and why, could you modify this correctly for me?
I updated Target.Column =1 to Target.Cell = F2 (this is the cell I have my first hyperlink in)
I updated ...Find("*", SearchDirection:=xlPrevious... to ...Find("C0 - Data Conversion - QA/UAT Combined\\GMTS", SearchDirection:=xlPrevious... (this is the actual text I am trying to match)
I updated every instance of Sheet2 to Report (my sheet name where I'm looking to highlight in color is Report)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cell = F2 Then
lastrow = Report.Columns(1).Find("C0 - Data Conversion - QA/UAT Combined\\GMTS", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Report.Range("A:A").Interior.Color = xlNone 'reset colors on destination sheet
For x = 1 To lastrow
If Report.Cells(x, 1) = Target Then
Report.Cells(x, 1).Interior.Color = vbYellow
End If
Next x
End If
End Sub

 

[Roderick_E]

Getting ready to head into a meeting but will help when I get out. Are all your links in the same column and it so, which?. Are all the results in the same column and if so, which? Thanks

 

[stressler]

Yes, all my hyperlinks are in column F of my Summary tab (F2:F43) are the exact fields I have hyperlinks in right now. And all my results to look through for a match are in column A on the Report tab. When I click on the hyperlink in each row, the hyperlink works to take me to the first row on the Report tab that contains that same text from column A same row as the hyperlink on the Summary tab. As an example: I have the hyperlink in field F2. The hyperlink formula is =HYPERLINK("#'Report'!"&ADDRESS(MATCH(A2,Report!A:A,0),3),"View"). Which is telling the hyperlink when I click the link in field F2, look for the text I have in field A2 on the Report tab in column A. Take me to the first instance of finding the text from field A2 on my Summary report and put me on the 3rd column. So the hyperlink is working perfectly. But I was hoping that when I clicked the hyperlink it would not only take me to the first instance of this text from field A2 on the Summary tab being found on the Report tab in column A, but it was also trigger the macro that highlights all the matches found for field A2 on the Summary tab the same color. I think you get the gist, I just don't know if I'm explaining it as clearly as I need to.

 

[Roderick_E]

Yes, all my hyperlinks are in column F of my Summary tab (F2:F43) are the exact fields I have hyperlinks in right now. And all my results to look through for a match are in column A on the Report tab. When I click on the hyperlink in each row, the hyperlink works to take me to the first row on the Report tab that contains that same text from column A same row as the hyperlink on the Summary tab. As an example: I have the hyperlink in field F2. The hyperlink formula is =HYPERLINK("#'Report'!"&ADDRESS(MATCH(A2,Report!A:A,0),3),"View"). Which is telling the hyperlink when I click the link in field F2, look for the text I have in field A2 on the Report tab in column A. Take me to the first instance of finding the text from field A2 on my Summary report and put me on the 3rd column. So the hyperlink is working perfectly. But I was hoping that when I clicked the hyperlink it would not only take me to the first instance of this text from field A2 on the Summary tab being found on the Report tab in column A, but it was also trigger the macro that highlights all the matches found for field A2 on the Summary tab the same color. I think you get the gist, I just don't know if I'm explaining it as clearly as I need to.

Thanks for the clarification, I thought I understood when you said all the hyperlinks are in column F and will link to the matching row on the other sheet in column A. Do you want to highlight just the rows in column 3 or in A-C?
This version highlight only third column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then
lastrow = Sheet2.Columns(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Sheet2.Range("A:C").Interior.Color = xlNone 'reset colors on destination sheet
For x = 1 To lastrow
If Sheet2.Cells(x, 1) = Target Then
Sheet2.Cells(x, 3).Interior.Color = vbYellow
End If
Next x
End If
End Sub

 

[stressler]

Thanks for the updated macro. I added it to my sheet3 (Summary) worksheet, but it isn't working. When I click on the hyperlink, it simply takes me to column C on the Report tab. So the hyperlink is working as intended, but the macro doesn't seem to be doing anything. Any thoughts on what I'm doing wrong?

 

[Roderick_E]

Thanks for the updated macro. I added it to my sheet3 (Summary) worksheet, but it isn't working. When I click on the hyperlink, it simply takes me to column C on the Report tab. So the hyperlink is working as intended, but the macro doesn't seem to be doing anything. Any thoughts on what I'm doing wrong?

Can you please paste in the macro here so I can see what modifications you made.

 

[stressler]

Unfortunately I didn't make any modifications to what you sent me last. Should I have updated something on it?

 

[Roderick_E]

Unfortunately I didn't make any modifications to what you sent me last. Should I have updated something on it?

Yes since I doubt your sheet1 and sheet2 is the same as mine.