Confused Changed Event

[mgw1138]

I have dropdowns on my sheet at I1, I6, I11, I16 and I21. When I select a new value in I1 from the dropdown box I immediately get the following error

"method "_default" of object range failed" After maybe 5 different errors Excel terminates.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim CA As Integer
CA = 0
If Not Intersect(Target, Range("I1")) Is Nothing Then CA = 1 < ------ Debugger stops here.
If Not Intersect(Target, Range("I6")) Is Nothing Then CA = 6
If Not Intersect(Target, Range("I11")) Is Nothing Then CA = 11
If Not Intersect(Target, Range("I16")) Is Nothing Then CA = 16
If Not Intersect(Target, Range("I21")) Is Nothing Then CA = 21
If CA = 0 Then Exit Sub

Dim X As Integer
For X = 2 To 7
If Cells(CA, 9) = Cells(CA, X) Then Exit For
Next X

' these next 5 lines do formating based on the dropdown activated.
Cells(CA, 9) = Cells(CA, X)
Cells(CA + 1, 9) = Cells(CA + 1, X)
Cells(CA + 2, 9) = Cells(CA + 2, X)
If Cells(CA + 3, X) = 1 Then Target.Font.Color = 0
If Cells(CA + 3, X) = 2 Then Target.Font.Color = 255
End Sub

 

[Coding4Fun]

You seem to have created a condition that is triggering an infinite loop.

In the instances where you set CA = 1 when you get to the line below you essentially trigger the loop to repeat.

If CA = 1 then you essentially update cell I1 = Cells(CA, X)

Cells(CA, 9) = Cells(CA, X)

This will result in it eventually crashing from the runaway loop.

You can see this if you put a break in the debugger and step through the code, it will hit this line

Cells(CA, 9) = Cells(CA, X)

And retriggers the change.

These lines never get called.

Cells(CA + 1, 9) = Cells(CA + 1, X)
Cells(CA + 2, 9) = Cells(CA + 2, X)
If Cells(CA + 3, X) = 1 Then Target.Font.Color = 0
If Cells(CA + 3, X) = 2 Then Target.Font.Color = 255

Here you can see I put a value in I1 which triggered the change, this will result in an infinite loop when it tries to update I1 again.

 

[mgw1138]

Thanks - I did not realize I was updating "I1"

As soon as I read your comment - it jumped out at me - thanks so much