Insert time on change in value within row - 2003

jw400

New Member
Joined
Aug 17, 2009
Messages
9
Hi all,
Im searching for a script to help me insert the current time value when a value within the row is changed. For example, if a value changes within the specified row, i.e. B1 changes, then within B12, the current date is inserted. However if B1 does not change then the old B12 value (old last change date) stays the same.

Im relatively new to VB and macro so any help would be appreciated!
Many thanks!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hello and welcome to MrExcel.

What changes B1 - does it contain a formula or are you changing it manually?
 
Upvote 0
Thanks for the reply
The script needs to cover changes across a whole row of upto 20 - 30 columns wide. The columns within the row contains manually inputed data and also certain VLOOKUPs.
Hope this helps!
Thanks again!
 
Upvote 0
Try this: right click the sheet tab, select View Code and paste in

Code:
Dim OldVal(30) As Variant
Private Sub Worksheet_Calculate()
Dim i As Integer
Application.EnableEvents = False
For i = 1 To 30
    If Cells(1, i).Value <> OldVal(i - 1) Then
        OldVal(i - 1) = Cells(1, i).Value
        Cells(12, i).Value = Date
    End If
Next i
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer
r = Target.Row
c = Target.Column
If r = 1 Then
    Application.EnableEvents = False
    If Cells(1, c).Value <> OldVal(c - 1) Then
        OldVal(c - 1) = Cells(1, c).Value
        Cells(12, c).Value = Date
    End If
    Application.EnableEvents = True
End If
End Sub

then press ALT + Q to return to your sheet.
 
Upvote 0
Thanks for your reply - I have inserted the code and it seems to work ok!

Just wondering if you can help me modify it slightly -
The column which will always display the date is column 29.
The first entry of the data is in row 6. the last entry of the data is in row 255.
The data which we are referencing any change exists in columns 2 to 28.

Is there any way I could ask your advice on how to apply this to the code as I have just realised these are the only columns affected.

many thanks!
 
Upvote 0
Now I'm confused :eek:

The code I gave you monitors changes in columns 1 to 30, row 1. When changes occur the date is written to row 12 of the same column.

Are you saying that you want to monitor a different row (6?) and write the date to a different row?
 
Upvote 0
Hi!
sorry for the confusion and the vagueness of the details given in my inital help request. It is only now that it has come to light what I actually need as I have figured it out more!

I have around 250 rows, each of which needs a date entry at the end of the row (in its column 29). The row in which the data first occurs is row 6 (previously row 1). And the data is spread across column 2 - 28.
So for each of the 249 rows (row 6 - row 255) I need a data to be generated when something in its respective row (columns 2 - 28) changes. This date is then shown in column 29 of the respective row.

Hope this makes more sense and I really appreciate this help!
 
Upvote 0
Try

Code:
Dim OldVal(249, 27) As Variant
Private Sub Worksheet_Calculate()
Dim i As Integer, j As Integer
Application.EnableEvents = False
For i = 6 To 255
    For j = 2 To 28
        If Cells(i, j).Value <> OldVal(i - 6, j - 2) Then
            OldVal(i - 6, j - 2) = Cells(i, j).Value
            Cells(i, 29).Value = Now
        End If
    Next j
Next i
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer
If Intersect(Target, Range("B6:AB255")) Is Nothing Then Exit Sub
r = Target.Row
c = Target.Column
    Application.EnableEvents = False
    If Cells(r, c).Value <> OldVal(r - 6, c - 2) Then
        OldVal(r - 6, c - 2) = Cells(r, c).Value
        Cells(r, 29).Value = Now
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
Just a problem has arisen today - I have noticed that everytime I open up the document - the date/ time changes ... as if it was =now()
I need it to stay the same from the previous change... any ideas?
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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