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.
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.
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
Martin
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.
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
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
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
Matin, it works perfect!!!
Thanks so much.
Hennie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks