Creating a search function

[Luke Emmett]

Hi,

So i have a rule running which searches my database and highlights information depending on what data i entered in the search cell, thats all sorted what im trying to do now is create a promt which comes up for you to enter the thing your searching for in turn this promt enters the data into a cell to be searched for but when ive done the Macro evertime i click off the cell it deletes the data in the cell thus clearing the search.

What i want it do is leave the data in that cell so i can look through the database but then if i click the Search button again it clears that cell to begin a new search ive tried adding a 'If IsEmpty' but i want it to skip the string if it there is no data in there but if there is clear it and prepare for a new search?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim numCell As Range


If IsEmpty(Range("G9").Value) = False Then
Range("G9").Clear
ElseIf IsEmpty(Range("G9").Value) = True Then GoTo SkipToHere
End If


SkipToHere:
If Target.Value = "SEARCH" Then
Set numCell = Target.Offset(0, 1)
numCell.Select
strPrompt = "Search"


On Error Resume Next
Num = InputBox(strPrompt, "Search", 0)
numCell.Value = numCell.Value + Num

End If
End Sub


Thanks in advance
Luke