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]

Sorry everyone,

Let me fix my code here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If (Target.Count = 1) And _
       (Not Intersect(Target, [F:F]) Is Nothing) Then _
        Target.Offset(0, 1) = Date
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Count = 1 And Target.Column = 6 Then
        If Target.Value = "Pass" Or Target.Value = "Fail" Then
            Target.Offset(0, 1) = Date
        End If
    End If


End Sub

 

[spydey]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Count = 1 And Target.Column = 6 Then
        If Target.Value = "Pass" Or Target.Value = "Fail" Then
            Target.Offset(0, 1) = Date
        End If
    End If


End Sub

Thanks there Fluff for the assistance. So the code works if I manually type in Pass or Fail the cell. However, I have a data validation dropdown list on those cells where I can pick and choose from Pending, Pass, Fail, or leave it blank. If I select Pass or Fail from the dropdown list, the date does not auto-populate. Any ideas?

Also, now that I have some sleep (very much needed), I think I might tackle this another way. I am going to explore both avenues and see what I can do. Your assistance is very much appreciated. If you have any thoughts or ideas regarding why the use of the data validation is not registering as a change in the target column, please feel free to shoot them my way.

Thanks again!

-Spydey

 

[Fluff]

What type of drop-down are you using?

 

[spydey]

Ok, so I figured out why the data validation wasn't working. The "Pass" & "Fail" in the code is case sensitive. I have "PASS" and "FAIL" for my data validation options, where as the code is "Pass" & "Fail". I think that I can add a few OR statements to the code and and have it work for any spelling of the words.

Something like this:

If Target.Value = "Pass" Or Target.Value = "PASS" or Target.Value = "pass" or Target.Value = "Fail" or Target.Value = "FAIL" or Target.Value = "fail" Then

Unless there is a way to have it coded so that case is not looked at.

-Spydey

 

[Fluff]

You can use this

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
        End If
    End If

End Sub

Ucase converts the target value into Upper case.

 

[spydey]

Ok, so I added modified a few lines of code and now it works great! Also, if the status of pass or fail is every changed, then the date is cleared out.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

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


End Sub

 

[spydey]

You can use this

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
        End If
    End If

End Sub

Ucase converts the target value into Upper case.

I will give that a try, thanks for your assistance Fluff, you have been most generous.

-Spydey

 

[Fluff]

Glad to help & thanks for the feedback