Check of the target cell value is matched to cells range value

Question

I would like to set a input checking in excel marco file. When user to input the column of recorded by and press enter, how to make VBA to check the target cell value (Column F:F) is matched one of cells range values (Column AB3:AD6)?

For example: 

Column F8 Jimmy <-> Column AB3:AD6   [TRUE]

Column F9 Johnny <-> Column AB3:AD6   [FALSE]

Thanks.

Answer 1

• Right click sheet tab > View Code > paste the code
• MsgBox is just a demo, modify as needed

Private Sub Worksheet_Change(ByVal Target As Range)
    Const LIST_RNG = "AB3:AD6"
    With Target
        If .CountLarge = 1 Then
            ' If Not Application.Intersect(Target, Me.Range("A1:B10")) Is Nothing Then
            If .Column = 6 And .Row > 7 And Len(.Value) > 0 Then
                Application.EnableEvents = False
                Dim c As Range
                Set c = Me.Range(LIST_RNG).Find(.Value, LookIn:=xlValues, Lookat:=xlWhole)
                If c Is Nothing Then
                    MsgBox "Bad"
                    ' Clear user input, modify as needed
                    .Value = ""
                    .Select
                Else
                    MsgBox "Good" ' modify as needed
                End If
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

Microsoft documentation:

Range.Find method (Excel)

Worksheet.Change event (Excel)