maybe this will help some

Public rngFound As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngSearch As Range

If Target.Address < "$B$3" Then Exit Sub

Set rngSearch = Me.Columns(1)
If Not rngFound Is Nothing Then
rngFound.Interior.ColorIndex = xlNone
Set rngFound = Nothing
End If

On Error Resume Next
Set rngFound = rngSearch.Find( _
what:=Target.Value, _
after:=rngSearch.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
matchbyte:=False, _
MatchCase:=False)
On Error GoTo 0
If Not rngFound Is Nothing Then
rngFound.Interior.ColorIndex = 6
End If

End Sub


I would qualify the remaining parameters of the Find method. If the
settings for LookIn, LookAt, SearchOrder, and MatchByte are not specified,
vba uses the settings from the users previous Find operation, which may have
unintended results on your macro.

When the workbook is closed and re-opened, the colorindex of the previous
found cell will not be set to xlnone (as the variable that references the
last found cell is destroyed when the workbook is closed). You could address
this through the Workbook_Open or Close event handlers, or change

If Not rngFound Is Nothing Then
rngFound.Interior.ColorIndex = xlNone
Set rngFound = Nothing
End If

To

rngSearch.Interior.ColorIndex = xlNone
Set rngFound = Nothing

in order to reset the colorindex of all of column 1, not just the last found
cell.



"Paul Findlay" wrote:

I've got some code that sort of works but has some bugs.

I've got a spreadsheet with some pricing in it. I've set up a cell to search
the product code which works although if the value is not found it returns
Error 91.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Then Exit Sub
Columns(1).Find(Target).Select
On Error Resume Next
End Sub

I'd like the cell it goes to, to be highlighted to draw the users attention
to it. I've got this code which works if I click on the cell but not through
the search function.

Public OldRng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng = Target
End Sub

Any help would be appreciated