I'd add a little more to it
1. You need Application.EnableEvents = False to prevent the event handler for triggering itself
2. It is possible that multiple col Y cells are changed at once (Paste, Control-Enter, etc.) so you would need to handle each one
3. If it's not found, then I just made the Region Red, although you could make the bad state red also
4. You could use Data Validation to make a drop down list since it's easy to misspell/mistype something
5. You could allow a state abbreviation in col Y, do the region lookup and also replace the NY with 'New York'
Option Explicit
Const cStateCol As Long = 25
Const cRegionCol As Long = 30
Dim rNames As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rStates As Range, rState As Range
Dim v As Variant
If Intersect(Target, Me.Columns(cStateCol)) Is Nothing Then Exit Sub
Set rStates = Intersect(Target, Me.Columns(cStateCol))
Set rNames = Worksheets("Names").Range("J:K")
Application.EnableEvents = False
For Each rState In rStates.Cells
With rState
If Len(Trim(rState.Value)) = 0 Then
.EntireRow.Cells(cRegionCol).ClearContents
.EntireRow.Cells(cRegionCol).Interior.Color = xlNone
Else
On Error Resume Next
v = Application.WorksheetFunction.VLookup(.Value, rNames, 2, False)
On Error GoTo 0
If IsEmpty(v) Then
.EntireRow.Cells(cRegionCol).ClearContents
.EntireRow.Cells(cRegionCol).Interior.Color = vbRed
Else
.EntireRow.Cells(cRegionCol).Value = v
.EntireRow.Cells(cRegionCol).Interior.Color = xlNone
End If
End If
End With
Next
Application.EnableEvents = True
End Sub