A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change cell colours using hyperlinks



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2009, 12:49 PM posted to microsoft.public.excel.misc
Lynda
external usenet poster
 
Posts: 160
Default Change cell colours using hyperlinks

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

  #2  
Old May 21st, 2009, 01:49 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 7,584
Default Change cell colours using hyperlinks

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

  #3  
Old May 21st, 2009, 02:25 PM posted to microsoft.public.excel.misc
Lynda
external usenet poster
 
Posts: 160
Default Change cell colours using hyperlinks

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" wrote:

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

  #4  
Old May 21st, 2009, 02:35 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 7,584
Default Change cell colours using hyperlinks

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

--
Gary''s Student - gsnu200854


"Lynda" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

  #5  
Old May 21st, 2009, 02:46 PM posted to microsoft.public.excel.misc
Lynda
external usenet poster
 
Posts: 160
Default Change cell colours using hyperlinks

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" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

  #6  
Old May 21st, 2009, 03:13 PM posted to microsoft.public.excel.misc
Lynda
external usenet poster
 
Posts: 160
Default Change cell colours using hyperlinks

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

Cheers
Lynda

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200854


"Lynda" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

  #7  
Old May 21st, 2009, 03:17 PM posted to microsoft.public.excel.misc
Bob I
external usenet poster
 
Posts: 10,698
Default Change cell colours using hyperlinks

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

Lynda wrote:

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" wrote:


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" wrote:


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


--
Gary''s Student - gsnu200854


"Lynda" wrote:


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


  #8  
Old May 21st, 2009, 03:28 PM posted to microsoft.public.excel.misc
Lynda
external usenet poster
 
Posts: 160
Default Change cell colours using hyperlinks

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" wrote:

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

Lynda wrote:

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" wrote:


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" wrote:


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


--
Gary''s Student - gsnu200854


"Lynda" wrote:


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



  #9  
Old May 21st, 2009, 03:45 PM posted to microsoft.public.excel.misc
Bob I
external usenet poster
 
Posts: 10,698
Default Change cell colours using hyperlinks

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

Lynda wrote:

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" wrote:


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

Lynda wrote:


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" wrote:



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" wrote:



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


--
Gary''s Student - gsnu200854


"Lynda" wrote:



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




  #10  
Old November 18th, 2009, 04:12 PM posted to microsoft.public.excel.misc
Marswii
external usenet poster
 
Posts: 2
Default Change cell colours using hyperlinks

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


"Lynda" wrote:

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" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200854


"Lynda" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.