Selecting Cells Each Side of a Selected Cell

[Targetpin]

I have a column of values D1:D200.
Using this =CELL("address",INDEX(D1:D200,MATCH(MAX(D1:D200),D1:D200,0))) it displays the cell with the largest value in.
I have been trying to using VBA to select the 10 cells above and below the displayed CELL reference and so far I have failed. The largest value will always be somewhere in the centre of the column of values (between D50 and D150). I am looking to chart the trends leading up to and passed the largest value. I have been trying Range and Cells with no success so any help would be very appreciated.

[ImStevenB]

Hi Targetpin,

Welcome to the Forum.

I am sure there are a couple of ways to select the cells you want. Here is the one that came up with

ActiveCell.Offset(-10).Resize(21).Select

This will offset 10 rows above the ActiveCell (or you can replace ActiveCell with your cell reference), and it "resizes" the selection to 21 rows (10 above + 10 below + the cell with largest value).

Hope this helps.

[Targetpin]

Thank you Steve. The sheet now works realy well.

Mark

[ImStevenB]

Glad it worked for you.

Don't forget to mark the thread Solved.