Worksheet_Change crashes Excel

Beard

New Member
Joined
May 24, 2015
Messages
14
I am most likely missing something, but I can't figure out what.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

For GRange = 0 To 20


    If Sheet7.Range("G" & 6 + GRange).Text = "X" Then
    
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 19
        'Sheet7.Range("R" & 6 + GRange).Value = Sheet7.Range("B2")
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 19
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 19
        
    ElseIf Sheet7.Range("G" & 6 + GRange).Text = "Y" Then
        
        Sheet7.Range("H" & 6 + GRange & ":" & "O" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Q" & 6 + GRange & ":" & "R" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("T" & 6 + GRange & ":" & "U" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("W" & 6 + GRange & ":" & "X" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("Z" & 6 + GRange & ":" & "AA" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AC" & 6 + GRange & ":" & "AD" & 6 + GRange).Interior.ColorIndex = 24
        Sheet7.Range("AF" & 6 + GRange & ":" & "AG" & 6 + GRange).Interior.ColorIndex = 24
        
    End If
    
Next
    
End Sub

If I remove the comment on the line
Code:
'Sheet7.Range("R" & 6 + GRange).Value = Sheet7.Range("B2")
Excel blows up.

I am obviously doing something wrong, but what?

I simply wish to place the value from B2 into the cell indicated. I'm most likely going into some infinite loop, but I can't see how?

Thanks in advance for any help.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Beard,

Try adding Application.EnableEvents = False as a first line and Application.EnableEvents = True as last line.

Otherwise, your worksheet change event is being triggered by itself every time it changes a cell value.
Thus it disappears up its own whotsit :)

You must always ensure that your Change event code resets Application.enableEvents to True from every potential code exit point otherwise events could be left disabled. Then you would need to reset via the Immediate pane of the vb editor by typing
Application.EnableEvents = True and hitting enter.

Hope that helps.
 
Last edited:

Beard

New Member
Joined
May 24, 2015
Messages
14
Beard,

Try adding Application.EnableEvents = False as a first line and Application.EnableEvents = True as last line.

Otherwise, your worksheet change event is being triggered by itself every time it changes a cell value.
Thus it disappears up its own whotsit :)

You must always ensure that your Change event code resets Application.enableEvents to True from every potential code exit point otherwise events could be left disabled. Then you would need to reset via the Immediate pane of the vb editor by typing
Application.EnableEvents = True and hitting enter.

Hope that helps.

This does indeed help.

Thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top