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!

 

[Ironman]

...And it does - sorted! Thanks ever so much Michael for identifying the issue and the cause of the "255/H1869" anomaly that's been bugging (ouch!) me all these years!

 

[Michael M]

Woo Hoo...glad we helped...

 

[Michael M]

Also....It might be an idea to start doing some housekeeping....I note there are at least 3 of these Subs in the workbook
You could also save ALL of the code to a new workbook and remove all of the redundant / commented code

Public Sub NewYearUpdate(ByVal iNewYear As Integer)

 

[Ironman]

Yes, I've wanted to tidy up the code for a long time - I have used the commented-out code in the past and didn't want to remove it in case I use it again in the future.

Re the above Sub - they were inserted by a much greater power than I as a solution to a number of year end updates I wanted to avoid doing manually - I don't know enough to risk amending any of those!

Thanks a lot once again!

 

[Michael M]

Paul

I have used the commented-out code in the past and didn't want to remove it

That's why I suggested copying to a new workbook, so you still have a copy of the code....then make the changes to your original.
There are also modules that are empty !

 

[Ironman]

Right, I'll have a think about that. Thanks for pointing out the empty modules, I'll certainly delete those.