Try the following procedure. Put it in the worksheet change event of the
appropriate worksheet. In this procedure cell C1 is where the user will
enter their value. If the value entered exists in column A then the program
will also place the value in cell B1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range, X As Range, Y As Range
Set X = ActiveSheet.Range("C1")
Set Isect = Application.Intersect(Target, X)
If Not Isect Is Nothing Then
For Each Y In ActiveSheet.Range("A:A").Cells
If Y.Value = X.Value Then
Application.EnableEvents = False
ActiveSheet.Range("B1").Value = X.Value
Application.EnableEvents = True
GoTo EndLine:
End If
Next Y
EndLine:
End If
End Sub

Hope this is a start.

Bill Horton

"alex" wrote:

I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex