Highlight multiple Selection Changes

Jaden C 1 Reputation point
2021-03-08T20:12:19.88+00:00

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!!

Small BASIC
Small BASIC
A programming language created by Microsoft that serves a stepping stone for beginners from block-based coding languages to more complex text-based languages.
269 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. WhTurner 1,596 Reputation points
    2021-03-09T11:01:36.543+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  2. Nothing Left To Lose 396 Reputation points
    2021-03-09T13:13:49.78+00:00

    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

    1 person found this answer helpful.
    0 comments No comments