cell.offset issue. Help please!

[RhodEvans]

Morning,

I have got some simple code for copying formatting from a reference row and pasting it into the row that is being used (done with worksheet change)

This uses offsets to figure out where to paste. This works well if a user hits the return key after entering information, but pastes in the wrong place if the user clicks in another cell or uses the arrow keys after entering information in the cell.

I assume that it is because it sees the active cell refernece as the cell that is now selected rather than the cell the information was originally entered in.

I have looked on the board and searched on Google, but have been unable to find a solution, and my VBA knowledge is not good enough to figure it out on my own. I would be immensly grateful to anyone who can point me in the right direction with this please.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim activer As Range
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
Set activer = ActiveCell
Debug.Print activer
Range("C2:AI2").Select
Selection.Copy
activer.Offset(-1, 2).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
    skipblanks:=False, Transpose:=False
activer.Offset(-1, 1).Select
Application.CutCopyMode = False
Range("O2").Select
   Selection.Copy
activer.Offset(-1, 14).Select
    ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub

 

[VoG]

Try

Set activer = Target

 

[RhodEvans]

Thank you very much for your speedy and very helpful answer. I had to add an 'on error resume next' line as vba threw up an'Application-defined or object-defined error' when deleting rows, and I had to slightly change the offsets to match the new target reference. But it works a treat.

thanks again!