Thank you.
That moved me along a little.
Now the debugger stops at
FirstAddress = FoundCell.Address
with "FirstAddress" highlighted.
Maybe I need a Dim staement for that as well?


On Oct 27, 4:22*pm, "Ron de Bruin" wrote:
I not see a dim line for ws

For the OP

Add *this line

Dim ws As Worksheet

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Patrick Molloy" wrote in message

...



sometimes errors liek this are misleading as they result from a completely
different, though non-the-less very serious problem.

Open TOOLS / REFERENCES *from the VBA editor menu and check to see if any
DLLs appear as MISSING
This could happen with an install/update

if any are, you'll need to browse to them, or, worst case, fix your excel
installation.

"Rick" wrote:

I have a macro that searches a worksheet for certain text then fills
the cell with a specified color. *I had help from this newsgroup a few
years back to write it and it has worked perfectly since then...until
yesterday.

When I run the macro I get an error and it drops me into a debugger
hightlighting the ws in the statement

*Set ws = ActiveSheet

The error says "Compile error: Can't find project or Library"

Is it possible that this is related to a patch that got applied?

Any help would be appreciated.

Here is the full Macro

Sub FindHiLight_V2()

* * * * * * * * * * 'I8 *Color19

* * 'Get search values from worksheet range.
* * Dim MyFind As Variant
* * Dim MyNewValue As Variant
* * Dim FoundCell As Object
* * Dim Counter As Long
* * Dim searchList As Range
* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I8")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 19
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * ' * * * * * I9 *Color49
* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I9")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 49
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I10 *Color22

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I10")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 22
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I11 *Color7

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I11")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 7
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I12 *Color3

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I12")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 3
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I13 *Color5

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I13")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 5
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I14 *Color24

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I14")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not FoundCell Is Nothing Then
* * * * FirstAddress = FoundCell.Address
* * * * Do
* * * * * * Counter = Counter + 1
* * * * * * '--------------------------------------------
* * * * * * '- what to do if found
* * * * * * FoundCell.Interior.ColorIndex = 24
* * * * * * '--------------------------------------------
* * * * * * Set FoundCell = ws.Cells.FindNext(FoundCell)
* * * * Loop While Not FoundCell Is Nothing _
* * * * * * And FoundCell.Address < FirstAddress
* * End If
* * rsp = MsgBox("Searched For: " & MyFind & Chr(13) & Chr(13) & _
* * * * * * * * "Found: " & Counter)
* * Next 'MyFind

* * 'I15 *Color6

* * '-------------------------------------------------
* * '- SET SEARCH KEY
* * '===== Change Range in next statement as needed =======
* * Set searchList = Worksheets("Instructions").Range("I15")
* * For Each MyFind In searchList
* * Counter = 0
* * '------------------------------------------------
* * '- FIND ALL MATCHING CELLS
* * On Error Resume Next
* * Set ws = ActiveSheet
* * Set FoundCell = ws.Cells.Find(what:=MyFind)
* * If Not

...

read more »- Hide quoted text -

- Show quoted text -