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:

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    AlterHyperLinkedRanged.Main Target
End Sub

Regular Module Named AlterHyperLinkedRanged

Option Explicit

Sub 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)

End Sub

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:

Option Explicit

Private Type Attrib
    Address As String
    WS As Worksheet
End Type

Private this As Attrib

Public Property Get Address() As String
    Address = this.Address
End Property

Private Property Let Address(value As String)
    this.Address = value
End Property

Public Property Get Worksheet() As Worksheet
    Set Worksheet = this.WS
End Property

Private Property Let Worksheet(value As Worksheet)
    this.WS = value
End Property

Function Parse(ByVal Subaddress As String)
    If Not (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))
    End If
End Function