Highlight multiple Selection Changes

Question

Hi there! I need some advice,
I have 2 worksheets in my document, the first sheet contains all unique values but the second has some duplicates. In order to show the connection, I've given all the duplicates in Sheet 2 a Defined name and hyperlinked to them on Sheet 1. This worked well as it allowed me to link a single cell on Sheet 1 to multiple cells on Sheet 2. The issue is that when I click the hyperlink and it directs me to the duplicates on Sheet 2, they're difficult to find as they're highlighted in grey. I would like to have all of the selections highlighted in a bold color. I've been able to find a code that will highlight one of the selections on Sheet 2 when I click the hyperlink but the other 2 or 3 are still grey. This is what I'm using on Sheet 1 (the sheet with the hyperlinks)

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveCell.Parent.Cells.Interior.ColorIndex = xlColorIndexNone
ActiveCell.Offset(0, 1 - ActiveCell.Column).Interior.Color = vbYellow
End Sub

This is what I'm using on Sheet 2 (the sheet with duplicates that the hyperlink directs to)

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Update 20140923
Static xLastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 6
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

This code works great for those that only link to 1 cell but any of those that link to duplicates on Sheet 2, will only highlight the first in yellow. Is there a way to have all of the Selection Changes highlighted instead of just 1?

Any help would be greatly appreciated!!

Answer 1

This is not a qiestion about the Small Basic language. I suspect it is about Excel.
Please remove trhe Smalll Basic tag.

Answer 2

re: "I've given all the duplicates in Sheet 2 a Defined name"

Have you tried coloring the named range(s)?...
Worksheets(2).Range("sludge").Interior.Color = vbYellow

NltL
https://1drv.ms/u/s!Au8Lyt79SOuhZ_2VvKCLZxz9iwI?e=vnEabM
Free: Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare, USA Lottery Numbers