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