Auto enter date

[Hennie]

Please refer attachment. In the header row(A), amongst other, the date
of the month appear. In the "Date Complete" column, I want the dat
under which the status was changed to "Passed" to automatically appea
in this cell

+-------------------------------------------------------------------
|Filename: Batch Status Report.pdf
|Download: http://www.excelforum.com/attachment.php?postid=5038
+-------------------------------------------------------------------

 

[mrice]

You need to paste this macro onto the appropriate sheet tab in the VB
editor (e.g. Sheet1")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target = "Passed" And Target.Columns.Count =
And Target.Rows.Count = 1 Then
Cells(Target.Row, 5) = Date
End If
End Su

 

[Hennie]

Martin,
Appreciate your quick response. May'be I must elaborate. The workbook
contains a spreadsheet for each month of the year and each spreadsheet
each date for the indivudual months. The "Passed" status can appear
under any of the dates for the months. The status is changed in the
individual cells based on the current status for a specific date.

 

[mrice]

Sorry, should have read your question properly!

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
If Target = "Passed" Then
Sh.Cells(Target.Row, 5) = Sh.Cells(1, Target.Column)
End If
End If
End Sub

It needs to go on the ThisWorkbook ta

 

[Hennie]

Martin, can you please elaborate on the location of the macro and also
if I can get it to run from a macro button?

Thanks,
Hennie

 

[mrice]

If you open the VBA editor (ALT -F11), you will see a tab for
ThisWorkbook on the left hand side.

Paste the code into this sheet.

There is no need for a button as the macro will run every time a cell
is changed.

On reflection you need a slightly more elaborate version to avoid
problems when changing values in the first five columns

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
If Target = "Passed" And Target.Column >= 6 Then
Sh.Cells(Target.Row, 5) = Sh.Cells(1, Target.Column)
End If
End If
End Sub

 

[Hennie]

Matin, it works perfect!!!
Thanks so much.

Hennie