Centering on a Hyperlinked cell

Hello fellow excel junkies,
I have the following formula in H4 in sheet 1:

=IF(COUNTIF(sheet2!$C$2:$C$300,$C2)>0,HYPERLINK("#sheet2!"&ADDRESS(MATCH($C2,sheet2!$C:$C,0),3),"Click Here"),"")

As you can see, H4 in sheet 1 will search column c in sheet2 for the value of cell C2 in sheet 1. I have the hyperlink working correctly in H4, so if it finds a match it will say "click here" and will take you to sheet 2 and highlight the matching cell in column C. however, it will not center on the cell, sometimes not even coming close.

Is there a way to use the hyperlink function that I have here, and just change/add to it so that it will center on the matching cell when I hit the "click here" text?

http://www.excelforum.com/showthread.php?t=634418
is a post i did some time ago it has code for centering when jumping via hyperlink
it should be able to be automted on click or similar

Originally Posted by martindwilson

http://www.excelforum.com/showthread.php?t=634418
is a post i did some time ago it has code for centering when jumping via hyperlink
it should be able to be automted on click or similar

Thanks for the code, do you have any suggestions for applying this code to a specific hyperlinked cell. I hate to complicate things (plus this may sound wierd) but my original example was only a freaction of my hyperlink formulas. I actually have 12 different cells that each search a different sheet in my workbook for a match with C2 on sheet 1, so H4 searches in sheet 2, I4 searches sheet 3, J4 searches sheet 4 and so on.

Is there a way to apply your VB code to all 12 cells?

Originally Posted by NBVC

A sample I did myself using your formula worked as expected highlighting the first occurance of a match to C2 in Sheet2!C:C.

It would be better if you supplied your own sample spreadsheet and tell us when it doesn't work as expected.

Its not that the function doesn't work, it will find a match without any problems. My issue is that i would like the screen to center on the matched cell. Depending on a number of factors, once I hyperlink the matched cell may be at the very top or bottom of the screen. Sometimes it is pretty close to centered, but I want it to be consistant.