Search and highlight

[Paul Findlay]

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

 

[JMB]

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]

Hi JMB,

Thanks for your help.

I should have clarified, I didn't write the code provided in my post, hence
I don't understand some of your points ie. those pertaining to qualifying the
parameters.

As such, the search doesn't return any values or change any cell colours.

Some more clarification would be appreciated but if it's too tedious for you
I understand.

 

[JMB]

not a problem - there a few things we can check

First, ensure you have a backup of your file in case anything goes awry.

I'm assuming the data to be highlighted is in Column A and item you want to
find is entered into cell B3.

The code itself goes into a worksheet module - not a standard vba code
module. Right click on the sheet tab and click view code and copy/paste the
code into the window that will appear. Be sure to delete any existing macros
in the code window that have the same name (ie - Worksheet_Change).

If this is all correct, double check that your input in cell B3 actually
equals the item you are trying to get it to find in Column A. In an empty
cell, enter =B3=A5 (assuming the corresponding match is in cell A5). If
False, maybe there are trailing spaces in the data or some other
non-printable characters (such as line breaks).