Track Cell Changes

[jamescooper]

Hello,

So I am looking for the most efficient way using VBA worksheet_change to track cell changes and populate into a column.

So say I have cells A1 and A2, when either of them change I want to populate it into another column, say C and D.

I will have quite a few of these so looking for the most efficient code under this:

Private Sub Worksheet_Change(ByVal Target As Range)

Many thanks.

 

[My Aswer Is This]

So are you saying anytime a value changes in column A you want something to happen in another column?

So if I enter Cake in A1 todays date will be entered in B1
If I enter Pie in A2 todays date will be entered in B2

And so on.

If this is not what you want the please explain more with specific details

 

[jamescooper]

So are you saying anytime a value changes in column A you want something to happen in another column?

So if I enter Cake in A1 todays date will be entered in B1
If I enter Pie in A2 todays date will be entered in B2

And so on.

If this is not what you want the please explain more with specific details

Thanks. So A1 and A2 will keep.changing. if either of these change I want it to record in column C for A1 and column D for A2....and it keeps populating in the next row underneath each other.


Hopefully that makes it clearer?

 

[My Aswer Is This]

So this will only involve A1 And A2

And if you enter Apple in A1 what do you want entered in Column C???
Do you also want Apple entered in column C

And if you enter Pear in A2 what do you want entered in Column D???

You said this:

and it keeps populating in the next row underneath each other.

so does that mean you A4 A5 etc.
​

 

[jamescooper]

Hello So

A1=Apple
B2 = Pear

C1 = Apple
D1=Pear

Then Apple or Pear in A1 or A2 changes

So

C1 = Apple
D1 = Pear
C2 = Banana
D1 = Pear

As A1 has changed on this occassion.

Thanks.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/13/2018  4:01:21 AM  EDT
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row = 1 Then Target.Offset(, 2).Value = Target.Value
If Target.Row = 2 Then Target.Offset(, 3).Value = Target.Value
End If
End Sub

 

[jamescooper]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/13/2018  4:01:21 AM  EDT
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row = 1 Then Target.Offset(, 2).Value = Target.Value
If Target.Row = 2 Then Target.Offset(, 3).Value = Target.Value
End If
End Sub

Thanks almost, when it changes now it overrides the old value rather than populating down the column?

 

[My Aswer Is This]

Well we are having a communication problem here.

Are you wanting this feature to work on any cell in column A

You said A1 and A2

You never mentioned A3 or any other row in column A

So if you want the script to work for row 3

What would we do on row 3?

Would the value go into Column A and E

 

[jamescooper]

Well we are having a communication problem here.

Are you wanting this feature to work on any cell in column A

You said A1 and A2

You never mentioned A3 or any other row in column A

So if you want the script to work for row 3

What would we do on row 3?

Would the value go into Column A and E

Sorry, maybe this explains it.


A C D
4 1.5 1.4 (1.5 starting value in A1, it then changes to 2 and then 4, populating in cells C1, C2, C3).
2.6 2 1.3 (Values in A2 populate in column D, e.g. started at 1.4, then 1.3, now 2.6).
4 2.6 But it pastes whenever A1 or A2 changes. So both don't have to change for it to populate in columns C and D.

 

[My Aswer Is This]

I deal better with Apple and Pear


So are you saying if you start by entering Apple in A1 you want Apple entered in C1
And then if you enter Pear in A1 you want Pear entered in C2
And then if you enter Banana in A1 you want Banana entered in C3

Is this what you want?

And the same with A2 but going into Column D