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

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
Code:
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

Board Regular
Joined
Jun 25, 2014
Messages
95
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

Board Regular
Joined
Jun 25, 2014
Messages
95

ADVERTISEMENT

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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.

Code:
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

Board Regular
Joined
Jun 25, 2014
Messages
95

ADVERTISEMENT

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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

Board Regular
Joined
Jun 25, 2014
Messages
95
Unfortunately I didn't make any modifications to what you sent me last. Should I have updated something on it?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,774
Messages
5,638,262
Members
417,018
Latest member
Khan kashaf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top