If / Then statement is creating an infinite loop when filling in a cell

[andrewgreen]

Hey everyone,

I'm working on a spreadsheet that uses dropdowns (Yes/No) in certain cells that turn other cells gray or enter text into specific cells.

For most of the cells, I just want the color to change. This works without a problem:

Please Login or Register  to view this content.

However, if I try to insert text into another cell, I get a long run-time error that says "Method 'Color' of object 'Interior' failed.

Please Login or Register  to view this content.

When I stepped through the code in debug mode it worked fine with just the colors, i.e. it would get to End Sub and stop. However, as soon as it gets to the part where it should insert the text, it skips back to the top of the Private Sub and loops through infinitely.

Any suggestions are appreciated. Thanks!

[StephenR]

I think you need to post all your code.

[andrewgreen]

Thanks for your reply.

This is the complete code:

Please Login or Register  to view this content.

[shg]

If you change the contents of a cell, you will retrigger the Change event.

Please Login or Register  to view this content.

[andrewgreen]

Hey,

Thanks for the response. Sorry for the late reply - got sidetracked on another project.

I'm new to programming, so I'm not sure how to go about this issue. Is there a way to change the contents of a cell without triggering the Change event?

Thanks

[Whizbang]

Shg has supplied the way to do this.

Please Login or Register  to view this content.