How can I keep my worksheet change event from cras

Question

![enter image description here][1]I'm constructing a table for scheduling baseball games using simple IF statements. Each team plays 8 games total, has to play 8 different teams, 4 home and 4 away.

The idea is that if a 1 is entered in B3 (Where A plays B), cell C2 fills with a 0 since two teams can't play eachother twice.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B3").Value = 1 Then Range("C2").Value = 0
If Range("B4").Value = 1 Then Range("D2").Value = 0
If Range("B5").Value = 1 Then Range("E2").Value = 0

So on and so forth. If I start filling out column B, it runs into some sort of loop and stops functioning until I bang escape or Excel crashes.

And if I use

Application.EnableEvents = False

The code stops working completely. How can I make this tool work "on the fly" without having it crash?

http://i.stack.imgur.com/itgsq.png

P.S. can't use solver for this because 15x15 is too many change cells

Answer 1

Whilst you have said you used:

Application.EnableEvents = False

Did you set it back at the end of your code with:

Application.EnableEvents = True

Put it just before End Sub. If you don't it will not fire again. The idea is turn the events off to make your changes then turn them back on again for the next time the sheet is updated