VBA Code to Match US Region to the State that is entered

[Paul_Hossler]

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