Worksheet_Change(Byval target as range) does not change the cells until I click the target range spesifically

Question

I am trying to write a VBA code that automatically augments the value in cell E11 based on a combination of the event of another cell changing to specific parameters.

My issue is that the VBA code I have written does this semi-automatically and not fully automatically. Is there any other way to make this occur automatically based on these if sentences

Private Sub Worksheet_Change(ByVal Target As Range)

    Set Actus = Range("B24")
    Set Def_F = Range("E11")


          If Target.Address = "$E$11" And Target.Value = "1" And Actus.Value = "CPTA" Then
                MsgBox "Try Again"
                Target.Value = "0"
                        ElseIf Target.Address = "$E$11" And Target.Value = "0" And Actus.Value = "DPTA" Then
                        MsgBox "Again, Again"
                        Target.Value = "1"
                              ElseIf Target.Address = "$B$24" And Target.Value = "CPTA" And Def_F.Value = "1" Then
                              MsgBox "Failed, Again"
                              Def_F.Value = "0"
                                     ElseIf Target.Address = "$B$24" And Target.Value = "DPTA" And Def_F.Value = "0" Then
                                     MsgBox "Having fun today?"
                                     Def_F.Value = "1"
            Else: Exit Sub
        End If





End Sub

Your question is not entirely clear. You say that it works if you click the target range(E11). So does that mean you want the code to also run if you change another range (B24)? If not, can you explain with an example? Also: please edit your spelling and grammar --Thanks! — Tony M
The code works when I click or make changes to the target range E11, but does but does not really respond if I make changes to B24. I have to click on E11 Before it proceeds to engage the code. I want it to automatically change based on those two criteria — Koko Etokebe

Tony M Tony M · Accepted Answer · 2018-03-22T21:57:44

To target more than one cell in the Worksheet_change event use Application.Intersect(Target, otherCell) as in this post.

Worksheet_Change(Byval target as range) does not change the cells until I click the target range spesifically

1 Answers