EXCEL: Auto update of Cell if ANOTHER Cell changes

  Heefie 18:50 09 Nov 04
Locked

I have a spreadsheet where I enter data into column B. I can then enter the command =Now() into the corresponding Column A Cell and this then gives me a record of all entries or amendments to the Worksheet, by date and time.

However, I'd like to Automate this so that if a Cell in Column B is changed in any way, the =Now() command is executed in the Corresponding Column A Cell.

The obvious statement is IF B1 has CHANGED THEN A1 = =Now() ... but the "CHANGED" command doesn't seem to exist (I'm using EXCEL 2000) ... I therefore assume that I'll have to create a Macro or VB script ...

... if so, could somebody please advise me as to how to do this, but bearing in mind that I'm a novice in EXCEL !!!!!

  cherria 08:47 12 Nov 04

Using your specific example, of wanting the time changed to be input into the cell directly to the left of the change being made.

The following code should work.

Right click on the sheet tab that you want this to apply to (sheet1, sheet2 etc.)

select the view code option

paste the following into the code sheet that appears


Private Sub Worksheet_Change(ByVal Target As Range)
Static runme As Boolean
If runme = False Then
runme = True
Cells(Target.Row, Target.Column - 1).Value = Now()
Else
runme = False
End If
End Sub

beware that this will run wherever you change a cell. You could modify it so that it only ran if the change was made in column B for example by changing the if to read

If runme = False and target.column=2 Then

Hope it helps

  cherria 08:50 12 Nov 04

oops, formatting has gone a bit awry

Private Sub Worksheet_Change(ByVal Target As Range)

Static runme As Boolean

If runme = False Then

runme = True

Cells(Target.Row, Target.Column - 1).Value = Now()

Else runme = False

End If

End Sub

  daba 00:43 30 Dec 04

so I hope Heefie spots this.

For a really cool change history, go to "Tools->Share Workbook : Editing tab", then check "Allow changes by more than one user....{etc.}" then go through the options on the "Advanced" tab.

You will also need to go to "Tools->Track Changes->Highlight Changes" and select "Everyone" in the 'Who' box so that you are included.

Changes can be highlighted on the cells that are changed, and hovering your mouse on the cell gives you the info. Or changes can be recorded to a list on a new worksheet.

For multi-user, this feature is fabulous, it tells you who changed what, from what, when they changed it etc. From a single-user perspective it at least tells you what the old value was before you overwrote it, making mistakes really easy to track down and rectify.

Well worth a play around with.

  daba 00:44 30 Dec 04

The 'where' selection in "Track Changes" can be set to limit recognising changes to the range of cells you are interested in.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…