Obtaining the Index Number of a Drop-Down List

Example

Sub test()
    Dim rng As Range, r As Range, s As String, x, temp, msg As String
    On Error Resume Next
    Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If rng Is Nothing Then MsgBox "No cell has Validation": Exit Sub
    For Each r In rng
        If r.Validation.Type = 3 Then
            s = r.Validation.Formula1
            If s Like "=*" Then
                x = Evaluate(Mid(s, 2) & "&""""")
            Else
                x = Split(s, ",")
            End If
            temp = Application.Match(CStr(r), x, 0)
            If Not IsError(temp) Then msg = msg & vbLf & r.Address(0, 0) & vbTab & temp
        End If
    Next
    If Len(msg) Then MsgBox "Cell" & vbTab & "Index" & msg
End Sub