Worksheet_Change将值与列表进行比较

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myList As Variant
    Dim cellValue As Variant
    
    ' 定义要比较的列表
    myList = Array("Apple", "Banana", "Orange")
    
    ' 检查发生改变的单元格是否在指定的范围内
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        ' 获取发生改变的单元格的新值
        cellValue = Target.Value
        
        ' 检查新值是否在列表中
        If IsInArray(cellValue, myList) Then
            ' 在这里执行相应的操作
            MsgBox "值有效！"
        Else
            ' 在这里执行相应的操作
            MsgBox "值无效！"
        End If
    End If
End Sub

Function IsInArray(ByVal value As Variant, ByVal arr As Variant) As Boolean
    Dim element As Variant
    
    ' 遍历列表中的每个元素
    For Each element In arr
        ' 检查值是否与列表中的元素相等
        If value = element Then
            IsInArray = True
            Exit Function
        End If
    Next element
    
    IsInArray = False
End Function