Go to certain cell on pressing return in a range

[CDandVinyl]

In the range I4:I300 , I'm trying to select the cell which is one below in the range D4:D300 once I press return.

For example , I would make my entry to I10 and press return. The cursor would then go to D11. A return on I234 would select cell D235 , and so on.

Can someone advice how to achieve this with VBA?

Grateful for any help.

[6StringJazzer]

Put this code in the VBA module for the worksheet that contains the cells you are talking about:

Please Login or Register  to view this content.

[CDandVinyl]

Thanks for getting back , and for the coding.

It's working when I select the cell and moving automatically without allowing any entry to the cell. I do need to put an entry in the cell in the range I4:I300 , and only move to D when return is pressed.

[TMS]

Maybe better changing it to a Worksheet Selection Change Event handler then.

[CDandVinyl]

Thanks . I've tried it in various events. No joy I'm afraid. (:

I'm selecting cell I36 for example and it's going to G37 rather than D37 as desired. On return the WS is shifting to the right also.

Ideally I'd like to chose for example I45 and make an entry. On pressing return D46 would be selected with no other movement in the WS. Grateful for your time and expertise.

[Croweater]

The code TMS provided does exactly as you have described in your requirements, except for the fact he got the column wrong by one.

Try changing the '-6' to '-5' and it should give you what you want.

[CDandVinyl]

Ok thanks. I changed -6 to -5 and that's fixed it. It's working fine now. A return in the range I4:I300 is moving the cursor to the correct position in D.

Unfortunately , as I is the last column to the right on my worksheet , hitting return in I throws the whole screen to the right. It moves the WS to show empty columns J and K. So the cursor is going left to D , but the screen is going right (!).

Is there a way with VBA to fix this so the screen stays still when i make a return in I?

[Croweater]

Can you put the sample workbook up (as per the yellow banner at the top of the page)?

[CDandVinyl]

I found a solution in setting

Please Login or Register  to view this content.

to limit the scroll on the page.This does the trick.

[TMS]

@CDandVinyl: Thanks for the rep.

@Croweater: I can't take credit for the code; that was provided by 6SJ.

[6StringJazzer]

Sorry for the error in the offset value.

There is no scrolling on my sheet, maybe because my monitor is bigger. On my sheet I see columns A:AC.

[Croweater]

@CDandVinyl: Thanks for the rep.

@Croweater: I can't take credit for the code; that was provided by 6SJ.

Yeah...my mistake. Apologies for that.

[TMS]

@Croweater: no worries.