Right click on the sheet tab and select view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim v As Variant, sVal As String
Dim sStr As String, lVal As Long
Dim sName As String, i As Long
If Target.Address = "$B$2" Then
v = Range("$H$2:$K$1522")
sVal = Left(Target, InStr(1, _
Target.Value, " ", vbTextCompare) - 1)
lVal = CLng(sVal)
sName = Right(Target, Len(Target) - (Len(sVal) + 1))
sStr = "Not Found"
For i = 1 To UBound(v, 1)
If StrComp(sName, v(i, 1), vbTextCompare) = 0 Then
If CLng(v(i, 3)) = 0 Or _
(lVal = CLng(v(i, 3)) And _
lVal <= CLng(v(i, 4))) Then
sStr = Format(v(i, 2), "00000")
Exit For
End If
End If
Next
Range("B3").Value = sStr
End If
End Sub


--
Regards,
Tom Ogilvy

"mikeburg" wrote in
message ...

You guys are a great help. In trying to decide which way to go, what
would be VBA code to accomplish arriving at the zip code. If possible,
I would like the VBA code to be the sheets change code. When the street
is keyed in, the corresponding zip code is looked up & put by it.

Don't give up on me. I am learning a lot from y'all. mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503623