If Target And Or Then - Pointers?

[spydey]

Hey there guys,

I am trying to get this worksheet_Change to work correctly for what I need. I am pretty tired so I am probably not thinking straight.

I have a drop-down list in column F, known as Status, it allows me to select a Pending, Pass, Fail, or leave the cell blank as the input. In column G, known as Date Completed, I am trying to input a date automatically when the contents of column F are changed to either Pass or Fail.

I can get a date in G if anything in column F changes, but I only want it to show the date when column F is either Pass or Fail. I just can't seem to get it. Too many long nights and not enough sleep lately.

Here is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    If (Target.Count = 6) And _
       (Not Intersect(Target, [G:G]) Is Nothing) Then _
        Target.Offset(0, 1) = Date
End Sub

I thought that I had it when I tried:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    If (Target.Count = 6) And _
       (Target = "Pass" OR Target = "Fail") And _
       (Not Intersect(Target, [G:G]) Is Nothing) Then _
        Target.Offset(0, 1) = Date
End Sub

But that was a no go.

Any thoughts or advise to point me in the right direction?

Thank you everyone!

 

[spydey]

@ Fluff,

Thanks so very much! Worked like a charm!

Here is my final code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Count = 1 And Target.Column = 6 Then
        If UCase(Target.Value) = "PASS" Or UCase(Target.Value) = "FAIL" Then
            Target.Offset(0, 1) = Date
        Else
            Target.Offset(0, 1).ClearContents
        End If
    End If


End Sub

Thanks again very much. I have only been working with VBA for about 2 weeks now and am learning a ton, so your willingness and disposition to assist me is very much appreciated.

-Spydey

 

[Fluff]