Click cell in Sheet 2 to create link to cell from Sheet 1 from text pasted from same cell in Sheet 1

[Ironman]

Hi

Sheet 1 Column G contains cells filled either red, yellow or green.

Sheet 1 Column H contains text.

My desired outcome is:

Be able to copy text from any cell in Sheet 1 Column H to any cell in Sheet 2 Column E, then

Click on the newly-copied text in Sheet 2 Column E.

Excel will then search Sheet 1 Column H and find the identical text (search up to 255 characters to prevent code mismatch errors)

The result will then create a link in Sheet 2 Column E back to the original cell in Sheet 1 Column H and will also now have the same red/yellow/green cell fill as the adjacent cell in Sheet 1 Column G.

I hope this is clear and I'd be over the moon if a solution can be provided.

Thank you!

 

[RasGhul]

Hi Ironman,

Do you have a sample to work from?

 

[Ironman]

Hi Rasghul, good to hear from you again bud!

I think it'll be easier to understand if you were to look at the file - here's the link https://www.dropbox.com/s/d0a7aj233gn35wp/Exercise Log.xlsm?dl=0

The relevant sheets are Training Log (Sheet 1) and Analysis (which is actually Sheet 3).

There's some 15 year old code in the Analysis sheet that I commented out because it will not work for Column E - the link always wrongly links to Training Log H1869 (uncomment it and try it for yourself on cells E1752:E1763 and you'll see what I mean) - I would LOVE to know why the links default to that cell! If they worked I wouldn't help on this as it still works for Column F but I don't want to use that column any more, I want it to work for Column E.

Here's the code:

'Courtesy of Tom Urtis 09.2004
'The following code locates comments in Training Log and when pasted into this sheet converts them to a hyperlink when clicked.

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
'If IsEmpty(Target) Then Exit Sub
'Application.ScreenUpdating = False
'Dim FindWhat$, FindWhere As Variant
'FindWhat = Left(Target.Value, 255) 'prevents 'type mismatch' error encountered up to 10.2005 with text entries >255 chars
'Set FindWhere = _
'Sheets("Training Log").Columns(8).Find(What:=FindWhat, LookIn:=xlFormulas, Lookat:=xlPart, MatchCase:=True)
'If FindWhere Is Nothing Then Exit Sub

'Dim iIndex%
'iIndex = Sheets("Training Log").Cells(FindWhere.Row, 7).Interior.ColorIndex
'Target.Hyperlinks.Add _
'Anchor:=Target, _
'Address:="", _
'SubAddress:="'Training Log'!H" & FindWhere.Row, _
'TextToDisplay:="Comments", _
'ScreenTip:="Go to Training Log H" & FindWhere.Row
'Target.Interior.ColorIndex = iIndex
'With Target
'.Font.FontStyle = Arial
'.Font.Size = 8
'.Font.Bold = True
'Application.ScreenUpdating = True
'End With
'End Sub

Many thanks RasGhul!

 

[RasGhul]

Hi mate,


VBA is not my forte but on the face of it the following lines are controlling the outcome, sorry I can't help more on this one Ironman;


'Sheets("Training Log").Columns(8) Column 8 is the H Column,

'Sheets("Training Log").Columns(8).Find(What:=FindWhat, LookIn:=xlFormulas, Lookat:=xlPart, MatchCase:=True)
'If FindWhere Is Nothing Then Exit Sub


FindWhere.Row, 7) Column 7 is the G Column

'Dim iIndex%
'iIndex = Sheets("Training Log").Cells(FindWhere.Row, 7).Interior.ColorIndex


It looks like this line is determining the Hyperlink address;

'SubAddress:="'Training Log'!H" & FindWhere.Row, _

 

[Michael M]

Can't download, but have you tried changing the hyperlink

'SubAddress:="'Training Log'!H" & FindWhere.Row, _
'TextToDisplay:="Comments", _
'ScreenTip:="Go to Training Log H" & FindWhere.Row

TO

'SubAddress:="'Training Log'![color=red]E[/color]" & FindWhere.Row, _
'TextToDisplay:="Comments", _
'ScreenTip:="Go to Training Log [color=red]E[/color]" & FindWhere.Row

 

[Ironman]

Rasghul - Many thanks for trying to help me

Michael - thanks to you too. The column you've suggested I change is correct - the comments are shown in Column H of Training Log. The links are needed in Column E of Sheet 3 (Analysis) P.S. I'm pretty sure the file should download.

 

[Michael M]

Ok...got it.
I'm more than a little confused.
This line

FindWhat = Left(Target.Value, 255)

is searching for the word "Comments" in the training sheet, which happens to be on row H1869, that's why the code refers to that cell ALL the time.

 

[Ironman]

Ahhhh that's really interesting to know - I've never understood what was unique about that cell! I just don't understand why it's searching for the word Comments.

As I mentioned, that code was written almost 15 years ago and I don't know if the 255 character thing is still relevant?

 

[Michael M]

Simple...when you click on the cell on the analysis sheet with the word "comments" in it, the code says

FindWhat = Left(Target.Value, 255)

, which is what this gives as Target.value...it will only find it in that cell on the training log.
so findwhat becomes "comments"
I think you need to match up the dates corractly then return the offset value from that matched date.
As per your other thread here

https://www.mrexcel.com/forum/excel...search-date-another-sheet-then-go-cell-2.html

 

[Ironman]

Thanks Michael - the other thread is an alternative (but for me, slightly less straightforward) method of linking back to the comments in Training Log.

The code in this thread always worked perfectly (and still does, even including the "255" line you identified) when the links were/are contained in Column F. Something is causing the "255" issue with Column E that didn't occur with Column F and I can't identify what it is, hence this thread.

Thanks again.

P.S. I've just realised - the cells in Column F contain the words "click here for comments", not simply "comments" - THAT's why I'm getting the problems. I will rename the cells in Column E and see if it works OK with the existing code.