Searching Based On Contents Of Changeable Cell

Hi - long story short - need to do a search based on the contents of a cell, but this cell wil change from minute to minute, so the result of that search will obviously differ each time.

So far, the relevant line of macro/VBA code is only searching for what was in the cell at the initial time of recording the macro, for example:





Cells.Find(What:="Allen", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Application.CutCopyMode = False




...searches for "Allen" each time, no matter what the contents of the cell which contained "Allen" initially, is now.

Many thanks in advance!

All you can do is get the search event to start again when there is a change on the sheet. Put the code into

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Will try that, many thanks!!!