Deciding where to put the active cell.

[praetorian]

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.

 

[P Sitaram]

Add this to the end of the 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]

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]

Can you post the entire code for the cange event? I will be able to look at it tomorrow.

 

[praetorian]

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.