Searching an Entire Workbook
 

Public Sub HighlightData()
Dim myDatum As Range, myRange As Range
Set myRange = Workbooks("List.xls").Worksheets("List").Range("Li st")

For Each myDatum In myRange
Windows("AllData.xls").Activate
Cells.Find(What:=myDatum, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
On Error GoTo ErrorHandler

Selection.Font.Bold = True
With Selection.Interior
..ColorIndex = 6 'Yellow
..Pattern = xlSolid
End With
Next
ErrorHandler:
ActiveSheet.Next.Select
Resume
End Sub

Can someone help me refine this code? It works quite well, but I
would like to replace the Error Handling with code that says what
is actually supposed to be done - that is, to select the next
sheet when the item being looked for is not on the current sheet
since this is something that is expected and not actually an error.
Also, there is the small problem (error) of it trying to select
the next sheet after the last sheet.

Searching an Entire Workbook
 

Apologies if you already knbew about it, but are you using a version of Excel
that doesn't allow you to select 'entire workbook' as the search area?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"SlimSlender" wrote in message
ps.com...
Public Sub HighlightData()
Dim myDatum As Range, myRange As Range
Set myRange = Workbooks("List.xls").Worksheets("List").Range("Li st")

For Each myDatum In myRange
Windows("AllData.xls").Activate
Cells.Find(What:=myDatum, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
On Error GoTo ErrorHandler

Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6 'Yellow
.Pattern = xlSolid
End With
Next
ErrorHandler:
ActiveSheet.Next.Select
Resume
End Sub

Can someone help me refine this code? It works quite well, but I
would like to replace the Error Handling with code that says what
is actually supposed to be done - that is, to select the next
sheet when the item being looked for is not on the current sheet
since this is something that is expected and not actually an error.
Also, there is the small problem (error) of it trying to select
the next sheet after the last sheet.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004