Cell Colouring

[mattie]

I am getting a bit stuck with changing the colour of some cells.

I have a link on sheet 1 that goes to a cell on sheet 2, what i am wanting to do is when the link goes to the cell on sheet 2 it changes the colour of that cell and the two next to it to highlight it.

I have had a try but i am not getting that far.

any help would be appericated!

Matt

 

[GlennUK]

Are you talking about a hyperlink?

 

[mattie]

I am yes, sorry i forgot to specificy the type of link i was using

 

[GlennUK]

And after the hyperlink is followed and the target cell ( and the two next to it ) has been highlighted, what is the action that will reset the highlighting on the cells? Will it be when any other cell there is selected afterwards? And maybe also when that sheet is deactivated? Or what?

 

[mattie]

The 3rd cell is a hyperlink back to sheet1 so once that is clicked the colouring will be reset. There are about 15 rows of text so i wanted the colouring to make it easier for the reader to see the wording for the link they selected on sheet1

 

[GlennUK]

Try putting this in the sheet code area, for the sheet that contains the first hyperlink ( right-click the sheet tab and choose View Code ):

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveCell.Resize(1, 3).Interior.ColorIndex = 3
End Sub

And put this code in the sheet code area of the target sheet, so as to reset shading:

Private Sub Worksheet_Deactivate()
    Me.UsedRange.Cells.Interior.ColorIndex = 0
End Sub