Highlight Target Cell With A Color Linked With A Hyperlink

I have implemented a functionality where there are dynamic hyperlinks across several rows. These HYPERLINKS point to different cells across different worksheets within the same wor

Solution 1:

Great problem! We can use worksheet events to handle this. The event that I feel is appropriate to use is the follow hyperlink event. Not sure how to set this up via python to be honest. But was fun working on it anyways! Goodluck.

Code is as follows:

The event itself, in Sheet that contains your hyperlink:

OptionExplicitPrivateSub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    AlterHyperLinkedRanged.Main Target
EndSub

Regular Module Named AlterHyperLinkedRanged

OptionExplicitSub Main(Target As Hyperlink)

    Dim ParsedSubAddress As ParsedSubAddress
    Set ParsedSubAddress = New ParsedSubAddress

    ParsedSubAddress.Parse Target.Subaddress

    Dim HyperLinkedRange As Range
    Set HyperLinkedRange = ParsedSubAddress.Worksheet.Range(ParsedSubAddress.Address)

    HyperLinkedRange.Borders.Color = RGB(0, 255, 0)

EndSub

And the class we created to store the data given to use by target.subaddress, AKA the range whose border we are looking to change. I created a new object to handle parse and store this data. I am doing this on the fly, feel free to improve! This code will go inside a class module.

Class Named ParsedSubAddress:

OptionExplicitPrivate Type Attrib
    Address AsString
    WS As Worksheet
End Type

Private this As Attrib

PublicPropertyGet Address() AsString
    Address = this.Address
EndPropertyPrivatePropertyLet Address(value AsString)
    this.Address = value
EndPropertyPublicPropertyGet Worksheet() As Worksheet
    Set Worksheet = this.WS
EndPropertyPrivatePropertyLet Worksheet(value As Worksheet)
    this.WS = value
EndPropertyFunction Parse(ByVal Subaddress AsString)
    IfNot (InStr(Subaddress, "!") > 0) Then
        this.Address = Subaddress
        Set this.WS = ActiveSheet
    Else
        this.Address = Mid(Subaddress, InStr(Subaddress, "!") + 1, Len(Subaddress))
        Set this.WS = Sheets(Mid(Subaddress, 1, InStr(Subaddress, "!") - 1))
    EndIfEndFunction