[Solved] Hyperlinked Fomula based Target Cell color to change

[santanukd]

Dear Experts,

I am using the below formula to create the Hyperlinks from the Source worksheet to Target Worksheet:

"HYPERLINK("#"&CELL("address",INDEX(Data,MATCH(H7,Data,0))),H7)"

Using the below VBA in Source worksheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveCell.Parent.Cells.Interior.ColorIndex = xlColorIndexNone
ActiveCell.EntireRow.Interior.Color = vbYellow

Using the below VBA in Target worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Cells.Interior.ColorIndex = xlColorIndexNone
End Sub

Target sheet row is not getting highlighted for the Hyperlinked Formula based links. However if I use a direct Hyperlink to a cell the VBA works perfect.
Please suggest what correction needed. Sample file added.

Regards

 

[Chihiro]

HyperLink formula works differently from standard Hyperlink. There is no hyperlink property for formula generated link and event will not be triggered.

Test this by adding debug.print in Source sheet code.
Ex:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Debug.Print "Code is triggered"
ActiveCell.Parent.Cells.Interior.ColorIndex = xlColorIndexNone
ActiveCell.EntireRow.Interior.Color = vbYellow
End Sub

There is no way to specifically trigger code from hyperlink click (that I know of) on formula generated links. You can alter code on Target sheet to something like, below to simulate. But it has side effect, of highlighting last active cell, when sheet is activated without using hyperlink.

Private Sub Worksheet_Activate()
ActiveCell.EntireRow.Interior.Color = vbYellow
End Sub

Private Sub Worksheet_Deactivate()
Me.Cells.Interior.ColorIndex = xlColorIndexNone
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Cells.Interior.ColorIndex = xlColorIndexNone
End Sub

 

[santanukd]

Works well.. Serves the purpose I wanted However it has 2 side effect

1. highlighting last active cell, when sheet is activated without using hyperlink
2. removing the row fill color for the header row (first row)

If this can be solved.. it will be rocking. If no then also its fine

 

[Chihiro]

1. No. I can't think of a workaround for this part.

2. You can use following code for Worksheet_Activate event.

Private Sub Worksheet_Activate()
If Intersect(ActiveCell, Rows(1)) Is Nothing Then
    ActiveCell.EntireRow.Interior.Color = vbYellow
End If
End Sub

 

[santanukd]

1. No. I can't think of a workaround for this part.

2. You can use following code for Worksheet_Activate event.

Private Sub Worksheet_Activate()
If Intersect(ActiveCell, Rows(1)) Is Nothing Then
    ActiveCell.EntireRow.Interior.Color = vbYellow
End If
End Sub

The above code didn't make any difference. Header row in the target sheet gets changed no fill everytime

 

[Chihiro]

Ah, so you need to add that line...
"If Intersect(ActiveCell, Rows(1)) Is Nothing Then"

And "End If" to all the subs in Target sheet. That will exclude 1st row from any change.

 

[santanukd]

Ah, so you need to add that line...
"If Intersect(ActiveCell, Rows(1)) Is Nothing Then"

And "End If" to all the subs in Target sheet. That will exclude 1st row from any change.

Still no success buddy.. as soon you click elsewhere in the Target Sheet the color of the header row in Target worksheet goes off

 

[Chihiro]

Upload sample with changes applied.

 

[santanukd]

File Attached

 

[Chihiro]

Ah, I see what you mean. Change Me.Cells.... part to something that's more defined.

Me.Cells will wipe color from entire sheet regardless.

Something like....

Private Sub Worksheet_Activate()
If Intersect(ActiveCell, Rows(1)) Is Nothing Then
    With Me.UsedRange
        .Rows(ActiveCell.Row).Interior.Color = vbYellow
    End With
End If
End Sub

Private Sub Worksheet_Deactivate()
Me.UsedRange.Offset(1).Interior.ColorIndex = xlColorIndexNone
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Rows(1)) Is Nothing Then
    Me.UsedRange.Offset(1).Interior.ColorIndex = xlColorIndexNone
End If
End Sub

 

[santanukd]

No success yet [U]Chihiro[/U] .. I keep the Target row 1 with some color and when go back to Source and hit Target worksheet color is white

 

[Chihiro]

Hmm? Worked fine on my end.

See attached.

 

[santanukd]

Hi Chihiro - This works like a charm now Well done.. Thank you so much ... Cheers

How do I mark this thread as Solved?

 

[Chihiro]

You are welcome
There's no function to mark thread as solved in this forum. I've edited the thread title.