|Well, since I can't get your code to fail or cause Excel to crash I can't offer any direct solutions. If Excel was crashing for me, I would add breakpoints to my code so that I could control the "run" as opposed to letting it just do it's own thing. By using a breakpoint the code will stop where I want it to and then I can single step through and watch what is happening. If I'm lucky, Excel with crash at the same instruction each time and that might give me a clue as to what is happening.|
I would like to comment on the wording of your post, just to make sure that you understand how a Worksheet_Change macro works. Maybe you just worded it poorly, and you do understand, but I just want to make sure. Maybe the interaction between the Worksheet_Change macro in each sheet is part of the problem.
You said: so on the first sheet if i change the cell to "complete" the macro runs. If i go to the other sheet and change that cell back to "In Progress" and then the macro runs.
In reality, as long as Events are enabled, the Worksheet_Change macro for a given sheet runs when you make any change to the sheet. For example, if you enter Fred in Complete!A1, VBA is going to see that the sheet has changed and the code stored in that sheet is going to run. The first instruction is:
If Target.Cells.Count = 1 Then
The If is going to be TRUE because you changed 1 cell, so the next instruction is going to be executed:
If Target.Column = 12 Then
That If is going to be FALSE because the change was made to Column 1, so the code will jump down to the End If for that instruction and eventually Exit. The macro didn't do anything other than execute those 2 instructions, but it most definitely ran.
The key point here is that the macro doesn't just run when you make the change that it is watching for, it runs whenever any change is made because it is watching for a change. Which instructions it executes will then depend on what the change was. You can verify that for yourself by using a breakpoint in the code and then making a change.
A subtle point here is that Worksheet_Change macro in the target sheet - the one where the row is pasted - does not run when the row is pasted, even though that is a change to that sheet. Doesn't that contradict what I just said?
Pop quiz: To prove that you understand how a Worksheet_Change macro works, explain to us why the Worksheet_Change macro in the target sheet doesn't run when the row is pasted into that sheet. Isn't that considered a change?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.