No problem, the error might be because there's a line
- Code: Select all
Call Call Macro1
I just copied and pasted your code but should be
- Code: Select all
Call Macro1
So the code would be something like
- Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then 'this is condition 1
Application.EnableEvents = False
If Range("Q1").Value = 1 Then 'this is condition 2
If Range("E2").Value = "In Play" Then ' this is condition 3
If Range("F2").Value = "Suspended" Then ' this is condition 4
If Range("R2").Value = Range("R1").Value Then ' this is condition 5
Call Macro1
End If ' this ends condition 5
ElseIf Range("F2").Value = "Closed" Then ' this elseif is within condition 4
Call Macro2
End If ' this ends condition 4
End If ' this ends condition 3
End If ' this ends condition 2
End If ' this ends condition 1
Application.EnableEvents = True
End Sub
With your code you have lots of loops within loops many of which can be simplified just adding conditions together with AND
What I am trying to achieve is the following;
If the following four conditions are met then Macro 1 is called.
Q1 = 1
E2 = In Play
F2 = Suspended
R2 = R1
For above you could write it in one line rather than having things in loops
- Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then 'this is condition 1
Application.EnableEvents = False
If Range("Q1").Value = 1 And Range("E2").Value = "In Play" And Range("R2").Value = Range("R1").Value Then
If Range("F2").Value = "Suspended" Then
Call Macro1
ElseIf Range("F2").Value = "Closed" Then
Call Macro2
End If
End If ' this ends condition 1
Application.EnableEvents = True
End Sub
That's assuming Macro2 is also reliant on Q1 = 1,E2 = In Play and R2 = R1.
VBA can be a pain but once you understand the basics it'll fall into place very quickly.