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

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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!
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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:

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!
 
Last edited:

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

TO

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

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Ok...got it.
I'm more than a little confused.
This line
Code:
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

Board Regular
Joined
Jan 31, 2004
Messages
186
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?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,257
Office Version
  1. 2013
Platform
  1. Windows
Simple...when you click on the cell on the analysis sheet with the word "comments" in it, the code says
Code:
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
 
Last edited:

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,990
Messages
5,526,096
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top