I don't see where you've initialized the Range
variable "c". Unless there's more to this code, I'd say
that's your problem.
-----Original Message-----

Hi,

I'm having a problem with the following code. Basically,
it keeps
falling over whenever I try to make the FIND bit run
within a loop.
I'm now getting an error message "Object Required" which
refers again
to the line the FIND bit is in.

Here's the code:

Sub copyProjectDates()
Dim sourceFile As Workbook
Dim DestFile As Workbook
Dim copyRange As Range
Dim destRange As Range
Dim foundCell As Range
Dim c As Range
Dim searchData As String

'ProjectRange = Range("B4:B42")
Set sourceFile = GetObject("H:\Schedule\Projects
Overview.xls")
For Each c In Range("B4:B42")

searchData = c.Value
With sourceFile.Sheets("Sheet1")
Set foundCell = Cells.Find(What:=searchData, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not foundCell Is Nothing Then
foundCell.Interior.Color = RGB(255, 0, 0)
Else
c.Interior.Color = RGB(0, 0, 255)
End If
End With
Next
sourceFile.Close SaveChanges:=False
End Sub

Any ideas gratefully received, Cheers, Jo


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.