Change cell colours using hyperlinks

[Lynda]

I have hyperlinks going from sheet 1 to descriptions in sheet 2. What I want
to happen is when I click on the hyperlink in sheet 1 that it will highlight
the cell in sheet 2 as yellow or some other colour. Is this possible?

Thanks in advance.
Lynda

 

[Gary''s Student]

Insert the following event macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveCell.Interior.ColorIndex = 6
End Sub

The macro will work if the hyperlinks were Inserted rather than =HYPERLINK()

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Lynda]

Thank you Gary''s Student. Your answer worked. Is it possible to modify the
code to highlight the active cell and the cell beside it as well? Thanks in
advance.
Cheers
Lynda

 

[Gary''s Student]

Delete the old macro and replace it with:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveCell.Interior.ColorIndex = 6
ActiveCell.Offset(0, 1).Interior.ColorIndex = 6
End Sub

 

[Lynda]

Sorry Gary''s Student but i need to remove the highlight on the cell become
unactive.

What i am doing is creating a survey and, if they need to, before the
participant answers a question they can click on the hyperlink and get a
description of what the question means. When they click on the hyperlink it
takes them to, for example, A1 which will have a title in it but then the
description is in B1. eg. (Sheet 2 - A1) Time management skills, (Sheet 2 -
B1) description of what time management skill are.

I hope this makes sense>

Cheers
Lynda

 

[Lynda]

Hi, the new code highlights the two cells that i wanted but does not
un-highlight the cells when they become unactive.

Cheers
Lynda

 

[Bob I]

have you thought about just using comments to provide the info?

 

[Lynda]

Hey, thank you Bob, no i hadn't thought of that, i should give it a try. It's
funny how you get your mind set on something and you forget to look outside
the square. Thank you again

Cheers
Lynda

 

[Bob I]

You're welcome, if it is sufficient, it should be much simpler to implement.

 

[Marswii]

I'm wondering if there is an answer to Lynda's original question... I used
the macro to highlight the target cell (from the hyperlink), but would like
to know if that cell can be 'un-highlighted' when you return to the original
tab?
I have a schedule of students attending our institution (they come & go
often) and linked their names to their pictures on another tab that gets
highlighted. Can it be done that when you go back to the original tab that
highlighted cell goes back to normal?
Thanks,
M