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
Post a Comment for "Highlight Target Cell With A Color Linked With A Hyperlink"