Deciding where to put the active cell.

praetorian

New Member
Joined
Jan 19, 2005
Messages
11
This is too difficult for me.

I have worksheet_change code that runs through a FOR EACH (CELL IN RANGE) loop. When the loop has finished I would like the macro to check where the active cell is and (this is the tricky bit):

If there was only one cell in the loop, and the active cell is in column F, then shift the active cell to Column J of the same row

or

If there were two or more cells in the loop, and the active cell is in column F, then (1) shift the active cell to Column D of the same row, then (2) bring a text box up reminding the user to close the workbook ready for the next user.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Add this to the end of the code:

Code:
If Not Intersect(Target, [F:F]) Is Nothing Then
        If Target.Cells.Count = 1 Then
            Cells(Target.Row, 10).Select
        Else
            Cells(Target.Row, 4).Select
            MsgBox "Save the workbook and close - NOW!"
        End If
    End If
 

praetorian

New Member
Joined
Jan 19, 2005
Messages
11
Sitaram

Thank you for your answer. Unfortunately it does not work.

Where only one entry is made, the active cell is staying on F, where there is more than one entry, the active cell is being moved to B - which was what was happening before, plus there is no Message.

Any ideas?
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Can you post the entire code for the cange event? I will be able to look at it tomorrow.
 

praetorian

New Member
Joined
Jan 19, 2005
Messages
11
Sitaram

Your code works now, thank you. There was an Exit Sub in the wrong place, which was shutting the macro down before your code was reached.
 

Forum statistics

Threads
1,147,688
Messages
5,742,627
Members
423,744
Latest member
bkirtland

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
Top