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

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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]
 

pcarvajal

New Member
Joined
Aug 1, 2013
Messages
6
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
Change the intersect to Column E then
Cells(Cell.Row,16).Value = Date
column 16 is "P"
 

pcarvajal

New Member
Joined
Aug 1, 2013
Messages
6
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
 

pcarvajal

New Member
Joined
Aug 1, 2013
Messages
6
A
B
C
D
E
F
E
H
I
J
K
1
HOURS
MILES
LAST SAFETY
DATE LAST UPDATED HOURS
DATE LAST UPDATED MILES
DATE LAST SAFETY
2
5464.00
1000.00
WED
1/30/13
2/2/13
4/20/13
3
654.00
200.00
THURS
2/25/13
3/4/13
3/13/13
4
6546.00
5654.00
FRIDAY
3/31/13
4/15/13
5/5/13
5
6546.00
654.00
SAT
4/25/13
6
123.00
555.00
SUN
7
32.00
66.00
8
665894.00

<tbody>
</tbody>


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
 

Forum statistics

Threads
1,136,954
Messages
5,678,757
Members
419,782
Latest member
gc75150

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
Top