CODEWITHSUNDEEP
excelvba
user11343272
user11343272

Reputation: 89

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

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]

enter image description here enter image description here

Thanks.

Upvotes: 0

Views: 56

Answers (1)

taller
taller

Reputation: 18778

  • 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)

Upvotes: 1

Related Questions