Combine multiple VBA IF formulas in worksheet

[Empirer]

Hi Excel wizards,

I'm trying to run this macro in the worksheet but only the first two if formulas are working. The last one if the condition is "Ceased" does not seem to work. Kindly advice on how to fix the VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      If Target <> "" Then
         Target.Offset(0, -6) = Now
      Else
         Target.Offset(0, -6) = ""
      End If
   ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
      If Target = "Completed" Then
         Target.Offset(0, -1) = Now
      ElseIf Target = "" Then
         Target.Offset(0, -1) = ""
      End If
    ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
      If Target = "Ceased" Then
         Target.Offset(0, 19) = Now
      ElseIf Target = "" Then
         Target.Offset(0, 19) = ""
      End If
End Sub

 

[aRandomHelper]

The last one if the condition is "Ceased" does not seem to work

Is this supposed to be on column J as well? If yes, it wouldn't work because you are using ElseIf, and the previous condition is already column J.

Also, it seems you are missing an "End If"

 

[Empirer]

Is this supposed to be on column J as well? If yes, it wouldn't work because you are using ElseIf, and the previous condition is already column J.

Also, it seems you are missing an "End If"

Yes this is based on Column J's value as well. I want it to populate a date in another cell if column J is "Ceased". How do I add another criteria while keeping the old ones?

 

[footoo]

If the Target is in column J and the Target = "", you have two different actions :
Target.Offset(0, -1) = ""
Target.Offset(0, 19) = ""

Which one do you want?

 

[footoo]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target <> "" Then
        Target.Offset(0, -6) = Now
    Else
        Target.Offset(0, -6) = ""
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If Target = "Completed" Then
        Target.Offset(0, -1) = Now
    If Target = "Ceased" Then
        Target.Offset(0, 19) = Now
    ElseIf Target = "" Then
        Target.Offset(0, -1) = ""
        Target.Offset(0, 19) = ""
    End If
End If
Application.EnableEvents = True
End Sub

 

[footoo]

Corrected

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target <> "" Then
        Target.Offset(0, -6) = Now
    Else
        Target.Offset(0, -6) = ""
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If Target = "Completed" Then
        Target.Offset(0, -1) = Now
    ElseIf Target = "Ceased" Then
        Target.Offset(0, 19) = Now
    ElseIf Target = "" Then
        Target.Offset(0, -1) = ""
        Target.Offset(0, 19) = ""
    End If
End If
Application.EnableEvents = True
End Sub

 

[Empirer]

Corrected

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target <> "" Then
        Target.Offset(0, -6) = Now
    Else
        Target.Offset(0, -6) = ""
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If Target = "Completed" Then
        Target.Offset(0, -1) = Now
    ElseIf Target = "Ceased" Then
        Target.Offset(0, 19) = Now
    ElseIf Target = "" Then
        Target.Offset(0, -1) = ""
        Target.Offset(0, 19) = ""
    End If
End If
Application.EnableEvents = True
End Sub

thank you so much, this one works!