Track Cell Changes

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
834
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:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Many thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
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

Code:
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top