How do I start a search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

everytime a open a certain spreadsheet?
I would like to search if several particular account numbers are on a
certain worksheet and then highlight it with yellow then until the whole
worksheet has been searched.
Now everytime a get a new spreadsheet, I need to do this search first to
alert me.

thanks a bunch.
 
If you only have a few account numbers you're looking for and they don't
change very often - it would be easiest to use conditional formatting.
Select all of the cells on the worksheet then go to format/conditional
formatting and set up your criteria (you have to set up a criterial for each
account number you're looking for). Of course, you would have to do this for
each worksheet you want evaluated.

An alternative is to set up a macro in your Personal Macro Library, which
may be a better route if you have a number of account numbers or you have
multiple worksheets/workbooks you need to review. Go into the Visual Basic
Editor (Alt F11) and paste the code at the bottom into any module of the
Personal Macro Workbook.

Then, switch back to Excel, unhide Personal.XLS (Windows/unhide) and enter
your account numbers in the first column of the first sheet (this is where
the macro assumes the account numbers will be). Then you can access the
macro through Tools/Macro/Macros (or Alt F8) and it will analyze the active
sheet for your account numbers. If you need to change your listing of
account numbers, you can unhide Personal.XLS and edit your list.

Sub FindAccounts()
On Error Resume Next
For Each x In ActiveSheet.UsedRange.Cells
If Not IsError(Application.VLookup(x.Value, _
ThisWorkbook.Sheets(1).Columns(1), 1, False)) Then
x.Interior.ColorIndex = 6
End If
Next x

End Sub
 
Back
Top