GoTo cell..

[Kashyap]

Hi, I have a Private Sub Worksheet_Change(ByVal target As Range) macro which
does something when value changes in a particular cell..

But when user input any value they may press right arrow or down arrow..

Is there a way to go back to cell in which value was entered recently? as I
need to perform some tasks over there..

 

[Gary''s Student]

Here is an example of detecting a change in B9, doing something, and then
returning to B9:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("B9")
If Intersect(r, Target) Is Nothing Then Exit Sub
MsgBox ("back we go!")
r.Select
End Sub

 

[AltaEgo]

Do you mean go back to the cell just changed? If so:

Private Sub Worksheet_Change(ByVal Target As Range)

'go back if value = 3
If Target.Value = 3 Then
Range(Target.Address).Activate
End If

End Sub


Note that the above code refers to Range(Target.address). You can make
changes or go to other cells using this.

Example 1, go two cells to the right of target :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = 3 Then
Range(Target.Address).Offset(, 2).Activate
End If

End Sub

Example 2, multiply the value by 2:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = 3 Then
'stay in the new address
'change the old value
Range(Target.Address).value = Range(Target.Address)*2
End If

End Sub

Almost forget to mention:

You need to wrap your code in an if condition to ensure it doesn't activate
when a range of cells are deleted or updated at the same time (paste, drag &
drop, etc)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Value = 3 Then
Range(Target.Address).Value = Range(Target.Address) * 2
End If
End If

End Sub