Right click sheet tabview codecopy/paste this
Now, if you type para in cell a1 you will go there

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Call gotoltr
End Sub
Sub gotoltr()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = UCase(Range("a1"))
ml = Len(x)
On Error Resume Next
For Each c In Range("a4:a" & lr)
If Left(Trim(UCase(c.Value)), ml) = x Then
Exit For
End If
Next
If UCase(Range("b1")) = "Y" Then Cells(c.Row, 1).Select
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sunilpatel" wrote in message
...
hi from a newbie

in column A i have a very long list of medical products in alphabetical
order.
I keep having to scroll up and down all day to find items. I would like a
sheet macro to detect a cell change e.g in B1 i enter "para" and column A
scrolls down until "Paracetamol" is visible on the screen in column A.
(i.e column A automatically scroll to line 400 where cell A400 is
Paracetamol).

Thanks in advance

Sunil