Current Code works, then when I add a line, Excel Crashes

[snowdyce]

Hello Experts. I am working on an excel with VBA code that someone else built. It currently works fine. I am trying to enhance it by adding some simple code. As soon as I add one line, and run it, Excel crashes completely. I don't get the debug error or anything, a pure crash, and it has been consistent. Tried restarting Excel and my computer just in case. I have no idea what is causing this. I did notice that this workbook has some code on the Sheet as well as the Model. Here is the code on the sheet, could this be the issue? I have always put all my code into the Module. I noticed it because when i tried to Debug it, it kept bouncing from the module to this code. I am unfamiliar with Case, I am a new VBA user.




Public Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Row

Case 4
Target.Font.ColorIndex = 1

Case 13

'MsgBox (" Worksheet Change")

If ActiveSheet.Cells(6, 1).Value = 1 Then
Target.Font.ColorIndex = 1
Else
Target.Font.ColorIndex = 3
Target.Interior.ColorIndex = 2
Target.NumberFormat = "###,##0"

End If

End Select

End Sub




Thanks,

[p45cal]

If there's no message it could be some other code is getting into a never ending loop or there's some code somewhere including the line Apllication.screenupdating=false which gets executed but never gets to its chum, Application.screenupdating=True.
Which line are you adding?
What versoin of Excel?
What are the symptoms of the crash?

[westconn1]

if your additional code changes cell values, it will fire worksheet change event (as above) for each edited cell
while the above code does not edit cells only change colours and numberformat, so should not create a loop, it is better to prevent it from running if not required

application.enableevents = false
before additional code, set to true after

if you want further assistance, you would need to show the module code procedure and the code you want to add

[snowdyce]

That Makes sense. I was able to get my code to work by using application.enableevents = false. During testing, another issue came up, I see why the original Developer added this Change Event. When the Cells are changed in Row 13, it should have red text and a white background. When I run through the code, I see it turn red, then white, but after the Sub ends, it defaults back to another format and I cannot figure out where this is happening. Any ideas or key words I can look for that would change the format again after the change event? Thanks again for your help!

[snowdyce]

I found the issue, I have a dynamic formatting sheet that was overriding the format after the change event. My issue is resolved, thank you both for the help.