Excel: How do I run a VBA script when a cell is updated?

Factual Questions
1

I am trying to get a date to autofill when a cell is updated.

So lets say I enter “6100” in cell D3 I want cell E3 to autofill the date (and then stay that way and not recalc till another amount is input in D3).

I see people all over wanting this and it seems unusually difficult. Anyway, I found the follow VB script:

*Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Intcolumn = Target.Column
introw = Target.Row
Cells(introw, Intcolumn + 1) = Now()

End If

End Sub*

I assume that will work but for the life of me cannot sort how to get it to run.

My Google-fu fails since it seems everyone who knows this stuff just assumes something as basic as making it work is something we all know.

Help! :smiley:

2

I can’t give you the answer off hand, but i would post the question here

3

Well, one of the problems appears to be that the script you have only works in column A, and if you want it to work in column D you have to change the


If Target.Column = 1 Then

to


If Target.Column = 4 Then

If you’re looking for the place to insert this code, I can help you out, if you let me know which version of Excel you’re using.

4

Thanks.

It is Excel 2007.

Think I got it to work with an Iterative function.

=IF(A1="","",IF(B1="",NOW(),B1))

Had to change a setting that allowed it to do a circular lookup but seems to be working.

I’d still like to know how to get that VB stuff working though.

Thanks again.

5

Excel 2007, right. cracks knuckles

First make sure you’ve got the Developer tab displayed: Microsoft Support
Then change to the Developer tab and click on the Visual Basic button
Right-click Sheet1 (or whatever sheet you’re working with) and click on View Code
Paste the entire macro in (including the Private Sub and End Sub lines) into the right-hand pane.
Click Save in the Visual Basic Editor and then close it.
Go back to your worksheet and check. With your original macro, any changes in column A should give you an instant update in column B.

6

Thx!

I’ll give it a go.