Date Stamp: recording date stamp in targeted cells when another cells is changed

P

pcarvajal

New Member
Joined
Aug 1, 2013
Messages
6
I ran into a problem in excel. I was hopingyou can help me. I have this code to enter a timestamp on a cell whenever Iupdate another and its working, but I also want to record a date stamp when Ichange data on another column other than column A. Like if I edit anythingin column C then date stamp adjacent cell in Column D. <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
The following code records a date stamp in the adjacent cellin column B when data is entered in column A:<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>
Dim Cell As Range<o:p></o:p>
For Each Cell InTarget<o:p></o:p>
With Cell<o:p></o:p>
If .Column =Range("A:A").Column Then<o:p></o:p>
Cells(.Row,"B").Value = Int(Now)<o:p></o:p>
End If<o:p></o:p>
End With<o:p></o:p>
Next Cell<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
I don’t know how to add another (IF and Then) code.<o:p></o:p>
<o:p> </o:p>
I’d really appreciate if you can help with this.<o:p></o:p>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Not Intersect(Cell, Union(Columns("A:A"), Columns("C:C"))) Is Nothing Then
        If Not Intersect(Cell, Columns("A:A")) Is Nothing Then
            Cells(Cell.Row, 2).Value = Date
        Else
            Cells(Cell.Row, 4).Value = Date
        End If
    End If
Next Cell
End Sub
 
Upvote 0
Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] Cell   [color=darkblue]As[/color] Range
    [color=darkblue]With[/color] Range("A:A,C:C")
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(.Cells, Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Intersect(.Cells, Target)
                [color=darkblue]If[/color] Cell.Value <> "" [color=darkblue]Then[/color] Cell.Offset(, 1).Value = [color=darkblue]Date[/color]
            [color=darkblue]Next[/color] Cell
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thank you so much, It seems to be working. Sw row, 2 REPRESENTS COLUMN B AND row 4 REPS COLUMN D. can i customize this per sheet on in a workbook. so say sheet two i would like to enter data on a cell in column E and record it on column P.
 
Upvote 0
pcarvajal said:
Thank you so much, It seems to be working. Sw row, 2 REPRESENTS COLUMN B AND row 4 REPS COLUMN D. can i customize this per sheet on in a workbook. so say sheet two i would like to enter data on a cell in column E and record it on column P.
Click to expand...
Change the intersect to Column E then
Cells(Cell.Row,16).Value = Date
column 16 is "P"
 
Upvote 0
Its been working great, but how can I keep adding More date stamps. so if i enter data in a cell in row f then have it automatically timestamp in row P
 
Upvote 0
[TABLE="width: 633"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl68, width: 67, bgcolor: transparent"]B
[/TD]
[TD="class: xl69, width: 93, bgcolor: transparent"]C
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl70, width: 85, bgcolor: transparent"]E
[/TD]
[TD="class: xl70, width: 85, bgcolor: transparent"]F
[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"]E
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]H
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]I
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]J
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]K
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]HOURS
[/TD]
[TD="class: xl68, bgcolor: transparent"]MILES
[/TD]
[TD="class: xl69, bgcolor: transparent"]LAST SAFETY
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 65, bgcolor: transparent"]DATE LAST UPDATED HOURS
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"]DATE LAST UPDATED MILES
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]DATE LAST SAFETY
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl68, bgcolor: transparent"]5464.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]1000.00
[/TD]
[TD="class: xl69, bgcolor: transparent"]WED
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]1/30/13
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]2/2/13
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]4/20/13
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl68, bgcolor: transparent"]654.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]200.00
[/TD]
[TD="class: xl69, bgcolor: transparent"]THURS
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]2/25/13
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]3/4/13
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]3/13/13
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl68, bgcolor: transparent"]6546.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]5654.00
[/TD]
[TD="class: xl69, bgcolor: transparent"]FRIDAY
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]3/31/13
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]4/15/13
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]5/5/13
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl68, bgcolor: transparent"]6546.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]654.00
[/TD]
[TD="class: xl69, bgcolor: transparent"]SAT
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]4/25/13
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl68, bgcolor: transparent"]123.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]555.00
[/TD]
[TD="class: xl69, bgcolor: transparent"]SUN
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl68, bgcolor: transparent"]32.00
[/TD]
[TD="class: xl68, bgcolor: transparent"]66.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]665894.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Can you help me get a code for this: in wich columns A B and C are the colums i enter data in and the corresponding columns e i and k have a date stamp automatically entered
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Insert new row with incremental formula
Replies
8
Views
479
Sheik
S
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
387
maiwarits
M
E
  • Question Question
Date/Time Stamp when a cell was changed (without VBA)
Replies
1
Views
387
Joe4
Joe4
Guzzlr
  • Question Question
Stripping Time Stamp away from Date
2
Replies
12
Views
630
Alex Blakenburg
A
A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
740
dreid1011
dreid1011

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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