Time stamping macro

[MisterK]

I am looking for help to automatically add timestamps (date of entry) to each row in a worksheet.

My goal is to test that there is data in cells in columns A, B and D, and when found, to enter the datestamp automatically after the user leaves cell D (and the others are filled) - the datestamp goes into cell H. I know it could be done manually with Ctrl - ;, but I really would be more efficient if it tested and automated the process.

Macro ideas for merging this change event with the testing of conditions?

Appreciate any help!

MisterK

 

[soccerkingpilot]

Hi MisterK,
Not sure I fully understand what you're trying to do, but this macro will automatically put today's date in col. Q if anything in that row has changed. This macro is stored in the sheet code to update live as users make changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.row > 6 Then Cells(Target.row, "Q") = Date
End Sub

Hope it helps,
Austin

 

[MisterK]

Thanks for this, but that's not quite it.

In my Excel sheet, each row contains the equivalent of a record. I don't want a time stamp in any row if there isn't data in the cells of that row that is non-blank. When the user has completed entering the data into the required cells (A, B and D), then what I want is for the spreadsheet to automatically add a non-changing date stamp to cell H in that row (essentially, the date stamp goes in when there is enough data in the row to qualify it as a valid record. Because users will be entering data across rows, I figure that if there is an "exit" or "change" event when the user leaves cell D (i.e., they have likely put data in cells A, B and D... although that should still be tested...), then the worksheet adds the date of entry in H because we have enough data in the row to call it "valid". Does that help?

MisterK

 

[montecarlo2079]

What about this?

[FONT=Times New Roman]Add this code to the specific worksheet that will be updated.[/FONT]
[FONT=Times New Roman] [/FONT]
[FONT=Calibri]Private Sub Worksheet_Change(ByVal areaOfInterest As Range)[/FONT]
[FONT=Calibri]  If areaOfInterest.Column = 5 Then[/FONT]
[FONT=Calibri]    If areaOfInterest.Row > 1 Then[/FONT]
[FONT=Calibri]    [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]        areaOfInterest.Offset(0, 5) = Now[/FONT]
[FONT=Calibri]        areaOfInterest.Offset(0, 5).NumberFormat = "dd MMM yy hh:mm AM/PM"[/FONT]
[FONT=Calibri]    End If[/FONT]
[FONT=Calibri]  End If[/FONT]
[FONT=Calibri]End Sub[/FONT]
[FONT=Times New Roman] [/FONT]
[FONT=Times New Roman]You need to play with the column and row numbers to show exactly what field would be changing.[/FONT]
[FONT=Calibri]An offset of 0,2 selects the cell two to the right of the cell that is changed. To use column B make the offset 0,1 and to use column H, make it 0,7. To change the column you’re interested in recording the time and date of the changes, change the column number in this line:[/FONT]
[FONT=Calibri]If areaOfInterest.Column = 1 [/FONT]
[FONT=Calibri]Remember, however, that the column that records the date is specified relative to this column so if one changes, both will.[/FONT]
[FONT=Calibri] [/FONT]