The main problem is that every time you store a value into AQ you are
generating a new change event, which again decides to put something in AQ,
which generates a new change event, etc.
You need to make sure that you only run the code if the change is for one of
the test columns. In my version of the code, I'd left I where it was, but
moved AI down to J so I could see it on the same screen. Hence my test
became
If Target.Column = 9 Or Target.Column = 10 Then
A couple of extra notes ...
You shouldn't pop up an error when you put something in column I but haven't
got around to filling AI yet. I don't know what test you'd use for this,
but it's very unfriendly to work with as it is.
You set the background red for an unacceptable value, but never set it back
to transparent when there is an acceptable value, so it stays red even when
OK.
The colon after the Else is stylistically wrong and will potentially result
in confusion when reading the code later. The rest of that line should be
moved to the next line ...
> Else
> Range("A" & row, "AR" & row).Interior.Color = RGB(255, 0,
> 0) 'If not any of those things, turn row RED
> response = MsgBox("The following is a list of acceptable values
> for the Rep/OSR field when the supplier is BIGsupplier" & vbCr _
> & "The value you have entered is not one of these allowed
> values. Please change this value.", vbOKOnly)
It is not good practice to use .FormulaR1C1 to store a value into a cell.
It could well be transformed if it looks like an R1C1 expression. To store
a value to a cell, use the .Value attribute; to store a "normal" formula,
use .Formula; to store an R1C1 formula, use .FormulaR1C1.