Worksheet_Change based on 2 conditions

[joee1975]

I have a code. It works well. However I need to make a change.
My code is based on having "completed" in column F
I cant figure out how to ADD a 2nd condition.
I need it to also have "Running Repair" in column E

Can someone please help?




Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As Range
Dim resp As VbMsgBoxResult
Dim i As Long

If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "Completed" Then
resp = MsgBox("Is unit returned to service?", _
vbYesNo + vbQuestion)
If resp = vbYes Then
Set f = Range("I:J").Find("RETURNED TO SERVICE", , xlValues, xlPart, , , False)
If Not f Is Nothing Then
i = f.Row + 2
Set f = Range("I:J").Find(Range("A" & Target.Row).Value, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
MsgBox "This unit already exists in the section."
Exit Sub
End If
Do While True
If Range("I" & i).Value = "" Then
Range("I" & i).Value = Range("A" & Target.Row).Value
Exit Do
End If
i = i + 1
Loop
End If
End If
End If
End If
End Sub

[rollis13]

Add this code at the end of your event macro.

Please Login or Register  to view this content.

[joee1975]

Sorry for not being clear, the condition at this moment is column F needs to be "Completed"

I need it to be column F "Completed" + column E "Running Repair"

Both conditions have to be at the same time

[bebo021999]

Should be:

.........declare variables 
If Target.Count > 1 Then Exit Sub 
If Intersect(Target, Range("E:F")) Is Nothing Then Exit Sub ' trigger target in E:F only 
If Cells(target.row, "E").value & Cells(target.row, "F").value = "Running RepairCompleted" Then ' combine value if both criteria 
... main code  

[joee1975]

what if I want "Running Repair" in column E, and "Completed" in column F?

[rollis13]

So isn't that what @bebo021999 's suggestion was for ?

[joee1975]

yes, sorry, I didnt see that post, I was looking at the other one, my mistake

[rollis13]

Thanks for the positive feedback, glad we were able to help.

[bebo021999]

It seems your issue has been solved. Pls mark it as "SOLVED"