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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hello and welcome to MrExcel.

What changes B1 - does it contain a formula or are you changing it manually?
 

jw400

New Member
Joined
Aug 17, 2009
Messages
9
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!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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.
 

jw400

New Member
Joined
Aug 17, 2009
Messages
9
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!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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?
 

jw400

New Member
Joined
Aug 17, 2009
Messages
9
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!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

jw400

New Member
Joined
Aug 17, 2009
Messages
9
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,291
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top