Using "Find" in a macro
 

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

--
icetre
-----------------------------------------------------------------------
icetrey's Profile: http://www.excelforum.com/member.php...fo&userid=2032
View this thread: http://www.excelforum.com/showthread.php?threadid=39449

Using "Find" in a macro
 

Not based on background color. You will need to loop throught the range of
cells and examine the background color. If the background color is
produced through conditional formatting, then you can't examine that
directly - you have to examine the underlying condition that produces the
color.

--
Regards,
Tom Ogilvy

"icetrey" wrote in
message ...

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

Using "Find" in a macro
 

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

Using "Find" in a macro
 

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