How to make Hyperlink which will change with cell number?

[jiura]

Hello to all!
There is one cell on Excel sheet witch has Hyperlink on itself (yes! on itself, beciouse I need handpoint as mouse Iconabove this cell). But user can add or del some cells above this cell. Becouse of this Hyperlink doesn't follow this cell number:
For example If my cell has Hyperlink on "C25" and then user Add two rows, it has Hyperlink on "C25" not on "C27", but I need Hyperlink on "C27"

[Bob Phillips]

Try adding an Excel Defined name to that cell, and then change the link in the hyperlink to the Defined Name rather than the cell reference.

[jiura]

Yes this is good, But I have a lot of such cells (they may be add or del )and I dn't want to add name to each of the cell

[jiura]

Hello to all again!
I solve this problem, and post my solution here:


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Range.Address), Address:="", SubAddress:=Target.Range.Address, ScreenTip:="Hi!
Range(Target.Range.Address).Select
Application.ScreenUpdating = True
End Sub


Thats all! NowHyperlink is always connect with one cell and link with it, and not depends of count of cells in above and left of this cell

[zamzam]

Dear jiura,

Can you please explain how to use your solution. I am not an expert in macros so please go easy on me.