Forgot about that enhancement in xl2002 - thanks for the correction.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Are you using xl2002 (or higher).

Then I got something recorded when I tried it manually.

And this is how I modified it:

Option Explicit

Sub testme()

Dim FoundCell As Range
Dim destCell As Range
Dim FirstAddress As String

Application.FindFormat.Clear
With Application.FindFormat.Interior
.ColorIndex = 6
End With

Set destCell = Worksheets("sheet1").Range("a1")

With Worksheets("Sheet2").UsedRange
Set FoundCell = .Cells.Find(What:="", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)
If FoundCell Is Nothing Then
'do nothing
MsgBox "not found!"
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Copy _
Destination:=destCell
Set destCell = destCell.Offset(1, 0)

Set FoundCell = .Cells.Find(What:="", _
After:=FoundCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End Sub

The .findnext() stuff seemed to forget about the searchformat parm.

icetrey wrote:

I have been trying to record a macro but when I select and use "Find" it
does not record. Is there any code to plug into a macro that will allow
me to Find and Select cells based on that cells background color then
Copy those cells to another location?

--
icetrey
------------------------------------------------------------------------
icetrey's Profile:
http://www.excelforum.com/member.php...o&userid=20329
View this thread:
http://www.excelforum.com/showthread...hreadid=394499

--

Dave Peterson