Couple of things....

Have you tried stepping through the code at all??

The code will be easier to read if indented and each "part" is on a seperate
line.
When it's seperated out you can step through the code much easier and see
what's going on.

FWIW, my personal preference... Is also to put all the Dims in one place.
I'd also change  all the literals to uppercase and test for the uppercase
values.
There's no value in selecting cells  to fill them/look in them, except in
tracking what's happening. Using ranges is usually quicker.
Most of that's personal preference though.

I suspect though that it's the syntax of the Find method that's the problem
though I can't quite see why at the moment.

I've seperated your code out below. What happens if you run it against your
sheet?


Start-------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target  As Range)

Application.ScreenUpdating = False

Dim LastRow
Dim row
Dim response

LastRow = Cells.Find _
("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row


For row  = 2 To LastRow
If UCase(Range("I" & row).Value) = "BIGSUPPLIER" Then

If UCase(Range("AI" & row).Value) = "ONETHING" Then

Range("AQ" & row).Select
ActiveCell.FormulaR1C1 = "L"

ElseIf UCase(Range("AI" & row).Value) = "OTHERTHING" Then

Range("AQ" & row).Select
ActiveCell.FormulaR1C1 = "X"

Else

'If not any of those things, turn  row RED
Range("A" & row, "AR" & row).Interior.Color = _
RGB(255, 0, 0)

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)
End If

End If

Next

Application.ScreenUpdating = True

End Sub
End

Also, look for any extra spaces in Columns AI or I. You might want to
TRIM the values  in your code.

I tried it with a static number, and it didn't work..

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.

Would setting Application.EnableEvents = False at the beginning and
setting it back to True at the end solve the loop issue? So would you
put the "If Target.Column = 9 Or Target.Column = 10 Then" above the
"If Range("I" & row).value = "BigSupplier" Then" line? I'll fix the
stylistic error, as well as the highlight error, change  it from
FormulaR1C1 to value and figure out how to limit the msgbox thanks!
(and then hopefully it will work >.> because on my screen it still
does nothing).

Still doesn't work... Again, supposed to, (if the worksheet_change was
on column  9, 35 or 43) if column I = "BIGSUPPLIER", depending on the
value of column AI, insert a value into column AQ (which may or may
not have said value already there). If column AI is not empty,
highlight row  as red and popup messagebox. Currently, on my
spreadsheet, nothing happens (whether or not the value in AQ is
already there, and whether or not AI is one of the listed values).

I commented out the find  functionality, so I know that's not the
problem... i just don't know what is the problem -_-

Sorry all, and thanks for the help.

------------------------------------------------

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target  As
Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Column = 9 Or Target.Column = 35 Or Target.Column = 43 Then

Dim LastRow
' LastRow = Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).row
Dim row
Dim response

For row = 2 To 3000 'should be LastRow

If Range("Mary!I" & row).value = "BIGSUPPLIER" Then
If Range("Mary!AI" & row).value = "THINGONE" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "Mary!A"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGTWO" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "Mary!A"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGTHREE" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "Mary!A"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGFOUR" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "D"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGFIVE" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "E"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGSIX" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "E"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGSEVEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "E"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGEIGHT" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "H"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGNINE" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "H"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGTEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "J"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGELEVEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "K"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGTWELVE" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "L"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGTHIRTEEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "L"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGFOURTEEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "L"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
ElseIf Range("Mary!AI" & row).value = "THINGFIFTEEN" Then
Range("Mary!AQ" & row).Select
ActiveCell.value = "O"
Range("Mary!B" & row, "Mary!AR" & row).Interior.Color =
RGB(0, 0, 0)
Range("Mary!A" & row).Interior.Color = RGB(204, 255, 255)
Else
If Range("Mary!AI" & row) <> Empty Then
Range("Mary!A" & row, "Mary!AR" &
row).Interior.Color = RGB(255, 0, 0)
response = MsgBox("The following is a list  of
acceptable values  for the Rep/OSR field  when the supplier is IBM" & vbCr _
& vbCr & vbTab & "THINGONE" & vbCr _
& vbTab & "THINGTWO" & vbCr _
'ETC
& "The value you have entered  is not one of
these allowed values. Please change  this value.", vbOKOnly)
Else
Range("Mary!AI" & row).Select
End If
End If
Else
Exit Sub
End If

Next

End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

I'm curious as to where you got that syntax for the Cell.Find method...
Could you give us a link or something????

You've put the right hand side of the test in uppercase but you've not made
sure the left hand side is also uppercase.
Example....
If Range("Mary!I" & row).value = "BIGSUPPLIER" Then
Should really be...
If UCase(Range("Mary!I" & row).value) = "BIGSUPPLIER" Then

Have you tried setting a breakpoint and stepping through the code to see
what happens??

Actually I'm assuming you know how to do that so just in case you don't....
Put the cursor on the first line of code and press F9
Or
Click in the grey vertical line to the left of the code window on
the first line of code.

You should see a red dot appear and the line highlighted in red.

When you run the code now then it will pause at that line and you can step
through it with F8.

That should show you which tests are being passed and which ones are
failing.
You can also look at and see the value of each of the variables or
expressions.

Personally I would use a Select Case construction rather than lots of
ElseIfs.

I got the cell.find method off a webpage, I imagine if you google it
you will get the page i got it from... but I don't remember where it
was... there were several methods for determining the last row  on the
page.

I don't really understand what you mean by the uppercase, actually,
I'm not sure why you would do that at all, can you tell me about it?
I mean, BIGSUPPLIER is not really the name I'm looking for, I had to
substitute everything because it is sensitive information. And we
would consider all BigSupplier = BIGSUPPLIER = bigsupplier =
bIgSuPpLier, regardless of case, if you get my drift, but in the
spreadsheet and in the code the cases match.

I completely forgot the Select Case construction existed... doh -_-
I'd rather leave it as it is than change  it, but yea, if I still can't
get it to work i might try that.

Which web page? Any idea at all?? I've searched extensively and
not found that syntax anywhere.

> there were several methods for
> determining the last row  on the page.
Yeah... I'd suggest you use a different one :-)

> I don't really understand what you mean by the uppercase,
> actually, I'm not sure why you would do that at all, can you
> tell me about it?
If the cases actually *do* match letter for letter then ok.... But can you
be *100%* positive of that *100%* of the time??

Uppercase stuff is to level the playing field  just in case there is a
difference in case in even *one* of the letters... by making *all* of the
letters the same case on both sides of the equation... Then there's no
possiblity of ...
If "BigThing" = "BIGTHING"

Because we've changed all the letters to upper case.
Using ucase means that ...
Ucase("BigThing") evaluates to "BIGTHING"

So ...
If Ucase("BigThing") = "BIGTHING"...
Actually is
If "BIGTHING" = "BIGTHING"

Did you have any trouble stepping through the code?????????
What happened??

I will change  it to the Ucase thing now that I understand it

And I haven't stepped through it yet, just got back to work (and
honestly, I don't know where yesterday went....)

If there is **Anything** that you don't understand or are unsure of then
just let us know.. We're all rooting for ya!!!

Thanks for the encouraging words, hopefully I can finally get this
thing working today >.>

All I have to say is there is a reason I was not a CS major, and this
is not in my job description *glares at boss* ;)

OK.. I think this script  needs lots of help!!! (sorry)
Let's start with this:
I "assume" the name of the SHEET is "Mary".
You're putting this change  event in the workbook, not in the sheet.
so that means that if you change a value in column  9 of sheet "tom",
this macro will run and highlight all of the errors in sheet "Mary"...

so, we need to iron out how you wish for the sheet stuff to work.

It's never a good idea to declare a variable with the same name as a keyword.
In this case, the use of "row" is ill-advised... You're relying on Bill Gates to
decide for you when you mean to use the actual "row" property of a
worksheet/range, and when to use the value of the variable you've specified.
I prefer to use something like Trow...

Next, You're hard-coding the "mary" sheet name
in the range of cells... I think there's a better way.

Next, the use of RGB.. I believe that the RGB(0,0,0) is clearing the interior
color.
I would suggest using: Interior.ColorIndex = xlNone
and Interior.ColorIndex = 3 to set  it to "red"...(I recorded a macro to get
these values!)

I think that the problem with your sheet "nothing happens" is probably because
you added the Application.EnableEvents = false.
That's fine for when everything is running, but while you're testing, if the
script "bombs",
you're left with the events turned off!
(I have a short macro in my Personal book to turn  them back on)


Looking at the macro from the start, you're trying to find  the last row  of data
by looking for a "*"...
Is there going to be a "*" in the last row?
How about this: Is there any columns that will ALWAYS have data?
If so, you can use a worksheet function to count the number of non-blank cells
LastRow = Application.WorksheetFunction.CountA(Range("A1:A65500"))

If that's not practical, since you're really concerned about the values  in the
"I" column, then
you really need the last row with a value in "I". Record a macro where you
select the cell I65500.
then hit the "end" key followed by the "up arrow". This will take you to the
last non-blank cell.
save the row as the Activecell.Row like:

CurCell = ActiveCell.Address
Range("I65500").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
Range(CurCell).Select

Here, I save the address of the currently active cell, go to a cell near the
bottom of column "I",
find the last non-blank cell, store it's row number, and return to the
previously selected cell.

Now, if you're wanting this to work with specific sheets, Like "Mary", "Peter"
and "Paul", then
you need to make use of the sheet index being passed to the macro.

also, rather than clearing background color  of each line, you can clear the
entire sheet at once:

Cells.Interior.ColorIndex = xlNone

I've cleaned it up a bit, and I think this may be easier to follow:


Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target  As Range)
Dim LastRow, Trow
Dim response
Dim CurCell
Dim ShtName

Application.EnableEvents = False
'Application.ScreenUpdating = False
ShtName = Sh.Name

If Target.Column = 9 Or Target.Column = 35 Or Target.Column = 43 Then
CurCell = ActiveCell.Address
Range("I65500").Select
Selection.End(xlUp).Select
LastRow = ActiveCell.row
Cells.Interior.ColorIndex = xlNone
Range(CurCell).Select

For Trow = 2 To LastRow
If Sheets(ShtName).Range("I" & Trow).Value = "BIGSUPPLIER" Then
Select Case Sheets(ShtName).Range("AI" & Trow).Value
Case "THINGONE"
Sheets(ShtName).Range("AQ" & Trow).FormulaR1C1 = "A"
' Sheets(ShtName).Range("B" & Trow & ":AR" &
Trow).Interior.ColorIndex = xlNone
Sheets(ShtName).Range("A" & Trow).Interior.Color =
RGB(204, 255, 255)
Case "THINGTWO"
Sheets(ShtName).Range("AQ" & Trow).FormulaR1C1 = "B"
Sheets(ShtName).Range("A" & Trow).Interior.Color =
RGB(204, 255, 255)
Case "THINGTHREE"
Sheets(ShtName).Range("AQ" & Trow).FormulaR1C1 = "C"
Sheets(ShtName).Range("A" & Trow).Interior.Color =
RGB(204, 255, 255)
Case Else
If Sheets(ShtName).Range("AI" & Trow) <> Empty Then
Sheets(ShtName).Range("AI" & Trow).Select
Sheets(ShtName).Range("B" & Trow & ":AR" &
Trow).Interior.ColorIndex = 3
response = MsgBox("The following is a list  of
acceptable values for the Rep/OSR field  when the supplier is IBM" & vbCr _
& vbCr & vbTab & "THINGONE" & vbCr _
& vbTab & "THINGTWO" & vbCr _
& "The value you have entered  is not one of these
allowed values. Please change this value.", vbOKOnly)
Else
Sheets(ShtName).Range("AI" & Trow).Select
End If
End Select
Else
Application.EnableEvents = True
Exit Sub
End If
Next
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

what she meant by that is that the test you're using is Case Sensitive.
Meaning that it will ONLY match if it is EXACTLY as you've listed it.
If you use instead:

ucase(sheets("Mary").range("I" & row).value) = "BIGSUPPLIER"
then whatever is in the cell will be forced to upper case before
comparing it to the name (you have to be sure and write the name in CAPS!)

Just thought of another technique that might REALLY REALLY REALLY simplify
your script. But only if certain conditions are true...

So, before I get into it...
In your script, You have a bunch of things  you're testing for in column  "AI"
(thingone, thingtwo,thingthree)
If you find  them, you're inserting something else in AQ...
What is the "something else".. is it another cell value or a constant?

The reason I ask is this:
VBA has a Dictionary object.
It's pretty basic, but VERY fast.

If you can use it, you set  it up like:

Dim THINGS, Stat

Set THINGS = CreateObject("Scripting.Dictionary")
Stat = THINGS.RemoveAll
THINGS.Add "THINGONE", "A"
THINGS.Add "THINGTWO", "B"
THINGS.Add "THINGTHREE", "C"
THINGS.Add "THINGFOUR", "D"
THINGS.Add "THINGFIVE", "E"
THINGS.Add "THINGSIX", "F"
THINGS.Add "THINGSEVEN", "G"
THINGS.Add "THINGEIGHT", "H"
THINGS.Add "THINGNINE", "I"
THINGS.Add "THINGTEN", "H"
THINGS.Add "THINGELEVEN", "J"
THINGS.Add "THINGTWENVE", "K"
THINGS.Add "THINGTHIRTEEN", "M"

then your entire loop becomes:

For Trow = 2 To LastRow
If Sheets(ShtName).Range("I" & Trow).Value = "BIGSUPPLIER" Then
If THINGS.exists(Sheets(ShtName).Range("AI" & Trow).Value) Then
Sheets(ShtName).Range("AQ" & Trow).FormulaR1C1 =
THINGS.Item(Sheets(ShtName).Range("AI" & Trow).Value)
Sheets(ShtName).Range("A" & Trow).Interior.Color =
RGB(204, 255, 255)
Else
If Sheets(ShtName).Range("AI" & Trow) <> Empty Then
Sheets(ShtName).Range("AI" & Trow).Select
Sheets(ShtName).Range("B" & Trow & ":AR" &
Trow).Interior.ColorIndex = 3
response = MsgBox("The following is a list  of acceptable
values for the Rep/OSR field  when the supplier is IBM" & vbCr _
& vbCr & vbTab & "THINGONE" & vbCr _
& vbTab & "THINGTWO" & vbCr _
& "The value you have entered  is not one of these
allowed values. Please change  this value.", vbOKOnly)
Else
Sheets(ShtName).Range("AI" & Trow).Select
End If
End If
Else
Application.EnableEvents = True
Exit Sub
End If
Next


If you need to add "things", then you just add them to the dictionary...

It's very fast with large numbers of items.
(I needed to read in a text file containing 145,000 records and needed to look
up data for 20,000 items.
Of course, I couldn't load it into a sheet and do a vlookup, so I read it and
loaded 6 dictionaries.
The whole lookup/update takes less than 90 seconds!)

Certainly try it. It will probably work. (I'm not a fan of disabling
events - hence didn't try that approach. Other people consider it the best
way to do this sort of thing.)

What I tend to do instead is to check the target's address and ignore the
event if I'm not interested in the cell. This is actually slightly more
complex and disabling events might be a cleaner solution.

I haven't read the whole thread, but regarding ignoring events; I
switched to a technique I learned here. I'm on Windows NT and it
seems to have very irregular event control using EnableEvents and
DoEvents. Since changing to this technique I have good control of
Change events.

Here's how.

Use a variable to abort the change  event handler when needed.

First.
Create a global variable called "Disabled".
It defaults to FALSE, so Change Events will execute right off.
[ You can even have several of these if you determine
you need to selectively disable different events. e.g.
ChangeDisabled, SelectionChangeDisabled, etc ]

Second.
When you want to ignore a Change Event, set  Disabled = True
Then change the cell value.

Sorta' like this:
Disabled = True ' Ignore Change
Cell(1,1).Value = 5
Disabled = False ' Back to normal


Third.
Early in your Event handler for value changes do like this:

Private Sub Worksheet_Change(ByVal target  As Excel.Range)
If Disabled Then Exit Sub
...
Bla Your Change handler code
...
End Sub


Make sure you exit gracefully. That is, take care of anything that
may have been set up for the Change sub and that needs to be un-done.
You can simply Goto a label right before the normal Sub cleanup (at
the end of the Sub).


If you need to do clean up differently than the normal exit does (I
did), you set up dual exits.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Disabled Then Goto MyExit
...
Bla, bla, bla Your Change handler code
...
Exit Sub ' The "normal" exit

MyExit: ' The abort exit
Cleanup
End Sub

I figured it out!

It was dumb -_-

It was exiting the If loop after checking row  2, because I am a moron,
and put the exit sub line inside the wrong if loop.

Thank you all for your help and suggestions!