Worksheet_Change crashes Excel

[Beard]

I am most likely missing something, but I can't figure out what.

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

'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.

 

[Snakehips]

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.

 

[Beard]

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.