need offset macro

[eddieg]

using this: Private Sub Worksheet_Change(ByVal Target As Range)
I need a macro that moves any activecell in the column J to move 4 cells to the left when i hit enter...... offset(0,-4)

thank you very much

 

[abdelfattah]

Hi
I'm not sure if you mean this(I prefer use select cell)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("J1:J10"), Target) Is Nothing Then ' expend range  if you need
    Target.Offset(, -4) = Target.Value
    End If
End Sub

 

[eddieg]

Hi
I'm not sure if you mean this(I prefer use select cell)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("J1:J10"), Target) Is Nothing Then ' expend range  if you need
    Target.Offset(, -4) = Target.Value
    End If
End Sub

This doesn't seem to work, thank you however.....let me clarify, I put text in each cell in the J column and when I hit enter, i want it to offset the the left 4 cells in the same row.

 

[abdelfattah]

I'm not sure why you insist use ENTER key!
what's the aim?!

This doesn't seem to work

you should be more specifically what happens for you.
the code depends on select cell then will copy the left 4 cells.
in sheet module doesn't contain Enter event to do that , I'm not sure if it's possible do that by create function , this is out of my capability, sorry!
you should wait for else body to help you.
good luck.

 

[AC PORTA VIA]

this?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("J1:J10"), Target) Is Nothing Then
    Target.Offset(, -4).Value = Target.Value
    End If
End Sub

 

[abdelfattah]

@AC PORTA VIA
If he wants this , then I misunderstood !

when i hit enter....

 

[eddieg]

Sorry, let me rephrase this.....as i enter text data in each cell in column J, then either tab or hit enter, i want it to activate the cell 4 to the left. so, if i type data in j5, hit enter, i want it to jump to f5.

 

[AC PORTA VIA]

This should work

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("J1:J10"), Target) Is Nothing Then
    Target.Offset(, -4).Select
    End If
End Sub