I was thinking the IF/AND statement would help, but not working. In the "Test Cases" worksheet, testers will select the status. If they select "Failed", I want a few things to happen in the "Defect" worksheet:

1. Date: Autopopulate the date in Defect worksheet
2. Title: Copy/paste the title from "Test Case" to the title in "Defect" ((Note for these: I need this to be static - even if the status in the "Test Cases" worksheet changes to a different status, I need these to remain as is)
3. I also need an email sent. If the Status = "Failed", I want an email sent to the Defect Resolution team so they have immediate notification

For the auto date population, I went to the "Defect tab" and in the Date cell, I entered: Excel Function
=IF(Test Cases !E1:E600="Failed", TODAY(), "")

Even though it failed, I had a concern with this one. In the Range of cells indicated, there could be multiple cells that were set to Failed during the day as testers were testing. I am not sure the above would set the date in the next available cell?

I couldn't find any examples to copy the information from one worksheet to the other when the specific condition was met.

For email

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address="$E1:E600" Then
If Target.Value\>Failed Then
Call SendEmail
End If
End If
End Sub

Sub SendEmail()
Dim OutApp      As Object
Dim OutMail     As Object
Set OutApp=CreateObject("Outlook.Application")
Set OutMail=OutApp.CreateItem(0)
With OutMail
.To="[email protected]"
.Subject="Automated Email: Threshold Exceeded"
.Body="A Test Case has been Set To Failed."
.Send
End With
Set OutMail=Nothing
Set OutApp=Nothing
End Sub

This spreadsheet sits out on SharePoint and not sure if that is impacting the VBA