Select Cell Based on another Cell Value

[lespaul00]

Hello,

I have a number "1" in cell Q2, and sequential numbers starting in L33 going down a few hundred rows. I would like the appropriate cell in Column L to be selected when it equals Q2, whenever Q2 is changed.

For example, if Q2 = 1, I want L33 to be selected automatically. If Q2 is changed to 2, I want L34 to be selected automatically. If Q2 = 5, I want L37 to be selected automatically.

Can this be done with a simply VBA script?

Thank you.

[Trebor76]

Hi lespaul00,

Is the number in cell Q2 the result of a formula (which you'll have to post for me to try and provide a solution) or just data entered?

Robert

[lespaul00]

Hello. Just entered data. I do have a button that causes the value in Q2 to increase or decrease by 1, but that's it.

[Trebor76]

Try this worksheet event macro on the sheet in question i.e. don't put the code in a standard module:

Please Login or Register  to view this content.

Regards,

Robert

[lespaul00]

Hello,

Thanks for the recommendation. However, it's not working. I did add this to the specific sheet's VBA, but it does not change the cell selection when the Q2 cell changes.

Here's a better explanation of what I am trying to do (since I realize my original explanation doesn't fully address what I am trying to do).

Below is an image of my sheet. I am plotting rows of data. As I change the value of Q2, the row of data to plot will change. It's a way to easily click through rows of data (with visual).

However, I would like the currently plotted row to be right up against my frozen pane at row 32. In my example below, I would like row 39 (the plotted row, shown with my conditional formatting), to be immediately after row 32 (since row 32 is my frozen pane). So, basically, I want the vertical scrolling to change as a function of the number in Q2 (which is the same as the Rank value in the third column).

example.jpg

I hope this is more clear!

[fshbet]

Hi, lespaul00

Small modification from "Trebor76", just change the ScrollColumn and ScrollRow based on your requirement.

Please Login or Register  to view this content.

[lespaul00]

Hello,

Thanks for the updated code. For some reason, I get a code mismatch error on the following line when executing a separate macro I have:

Please Login or Register  to view this content.

Also, your code works, but only if I change the value in Q2 manually. I also have a "spinner" activex Up/Down arrow button that changes the value of Q2 in increments of 1. When I change the cell value using this, your code does not work.

Any thoughts on these two items?