Right click on Sheet tab, view code, paste the following in. Note that macro
checks whenever you make an edit (not simply tab over) the cell in column D.
Also, Fail is case-sensitive ("fail" will not trigger event).



Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
'Column D corresponds to 4
If Target.Column = 4 And Target.Value = "Fail" Then
Target.EntireRow.Copy
Sheets("Notes").Select
i = 3
FindBlank:
'Checks to find first blank row
If Worksheets("Notes").Range("D" & i).Value = "" Then
Worksheets("Notes").Range("A" & i).Select
ActiveSheet.Paste
Else: i = i + 1
GoTo FindBlank
End If
Application.CutCopyMode = False
Sheets(StartSheet).Select
End If
Application.ScreenUpdating = True

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"hshayh0rn" wrote:

I would like to have an IF state that says something to the effect:

IF D4 = "Fail" then copy row4 to "Notes" (notes is another sheet in the
workbook)

This statement would repeat for every row on the sheet and needs to be real
time. AS I tab off of column D each time if "Fail" is the value in that cell
then the row should be copied. The first available row on the notes sheet is
row 3.