Change Event - Cell Selection

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160
Is there a way to select a cell depending on what cell has been changed. In other words if a user changes cell A1 I would like cell C1 to be automatically selected. In turn if user changes C1 I would like cell E5 to be selected. Any suggestions would be very helpful. Have a great day.

Steve
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Right-click the tab of the sheet you want, and select 'View Code.' This will open the Visual Basic Editor.

On the screen that comes up, paste the following code:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> myErr

Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> Application.EnableEvents = True: <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "$A$1"
        [C1].Select
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "$C$1"
        [E5].Select
<SPAN style="color:#00007F">End</SPAN> Select

Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

myErr:
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps,
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        Range("C1").Select
    ElseIf Target.Address = "$C$1" Then
        Range("E5").Select
    End If

End Sub
 
Upvote 0
Thanks a bunch for such a quick response. I will give both of them a try and see which one works the best. Have a great day.

Steve
 
Upvote 0
I have a similar problem to Scrandal and tried both codes but got error messages on both... need help forcing the user to go from column H to column I. Depending on what tab/ enter settings they do not necessarily end up there but its important that if the make a change in either column, they have to be directed to the other.... Help...
 
Upvote 0
Maybe try:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> Application.EnableEvents = True: <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = 8
        Cells(Target.Row, "I").Select
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = 9
        Cells(Target.Row, "H").Select
<SPAN style="color:#00007F">End</SPAN> Select
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top