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


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

--
Hennie
------------------------------------------------------------------------
Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399
View this thread: http://www.excelforum.com/showthread...hreadid=561829

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

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=561829

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.


--
Hennie
------------------------------------------------------------------------
Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399
View this thread: http://www.excelforum.com/showthread...hreadid=561829

Sorry, should have read your question properly!

Try this

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" Then
Sh.Cells(Target.Row, 5) = Sh.Cells(1, Target.Column)
End If
End If
End Sub

It needs to go on the ThisWorkbook tab


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=561829

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


--
Hennie
------------------------------------------------------------------------
Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399
View this thread: http://www.excelforum.com/showthread...hreadid=561829

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=561829