Track Cell Changes

jamescooper

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

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
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

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 8, 2014
Messages
780

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
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
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
780

ADVERTISEMENT

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?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
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-known Member
Joined
Sep 8, 2014
Messages
780
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top