Is it possible to find and replace by format?

I have a workbook with many worksheets. I would like to search for all
cells with a fill color of "yellow" and change to "No Fill". Is this
possible?

Thanks.

This was tested in xl2002

Sub ReplaceByFormat()
Application.FindFormat.Clear
Application.FindFormat.Interior _
.ColorIndex = 6
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior _
.ColorIndex = xlNone

Cells.Replace What:="", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True
End Sub

The capability was introduced in xl2002, so it should work in xl2003, but
not in versions earlier than xl2002.

--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Is it possible to find and replace by format?

I have a workbook with many worksheets. I would like to search for all
cells with a fill color of "yellow" and change to "No Fill". Is this
possible?

Thanks.

Tom,

Thank you for your quick reply.

I committed a major blunder by not stating what version of Excel I am using.
Unfortunately, I am using xl2000, so ReplaceFormat is not available to me.

Is there any way to accomplish this is xl2000?

Gordon

"Tom Ogilvy" wrote:

This was tested in xl2002

Sub ReplaceByFormat()
Application.FindFormat.Clear
Application.FindFormat.Interior _
.ColorIndex = 6
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior _
.ColorIndex = xlNone

Cells.Replace What:="", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True
End Sub

The capability was introduced in xl2002, so it should work in xl2003, but
not in versions earlier than xl2002.

--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Is it possible to find and replace by format?

I have a workbook with many worksheets. I would like to search for all
cells with a fill color of "yellow" and change to "No Fill". Is this
possible?

Thanks.

No. It was introduced in xl2002.

You would basically need to loop through all you cells, test if the cell has
interior.colorindex = 6 and change it to xlnone if it does.


For each cell in activesheet.usedrange
if cell.interior.colorindex = 6 then
cell.interior.colorindex = xlNone
end if
Next


--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Tom,

Thank you for your quick reply.

I committed a major blunder by not stating what version of Excel I am
using.
Unfortunately, I am using xl2000, so ReplaceFormat is not available to
me.

Is there any way to accomplish this is xl2000?

Gordon

"Tom Ogilvy" wrote:

This was tested in xl2002

Sub ReplaceByFormat()
Application.FindFormat.Clear
Application.FindFormat.Interior _
.ColorIndex = 6
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior _
.ColorIndex = xlNone

Cells.Replace What:="", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True
End Sub

The capability was introduced in xl2002, so it should work in xl2003,
but
not in versions earlier than xl2002.

--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Is it possible to find and replace by format?

I have a workbook with many worksheets. I would like to search for
all
cells with a fill color of "yellow" and change to "No Fill". Is this
possible?

Thanks.

Thanks Tom. That did the trick.

For posterity, here is my code (tested in xl2000).

' Running this macro will find all cells in the workbook with a
' Yellow fill color (6) and clear the fill.
Sub ClearYellowFill()
For Each Sheet In Worksheets
Sheet.Activate
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 6 Then
cell.Interior.ColorIndex = xlNone
End If
Next
Next
End Sub

"Tom Ogilvy" wrote:

No. It was introduced in xl2002.

You would basically need to loop through all you cells, test if the cell has
interior.colorindex = 6 and change it to xlnone if it does.


For each cell in activesheet.usedrange
if cell.interior.colorindex = 6 then
cell.interior.colorindex = xlNone
end if
Next


--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Tom,

Thank you for your quick reply.

I committed a major blunder by not stating what version of Excel I am
using.
Unfortunately, I am using xl2000, so ReplaceFormat is not available to
me.

Is there any way to accomplish this is xl2000?

Gordon

"Tom Ogilvy" wrote:

This was tested in xl2002

Sub ReplaceByFormat()
Application.FindFormat.Clear
Application.FindFormat.Interior _
.ColorIndex = 6
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior _
.ColorIndex = xlNone

Cells.Replace What:="", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True
End Sub

The capability was introduced in xl2002, so it should work in xl2003,
but
not in versions earlier than xl2002.

--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Is it possible to find and replace by format?

I have a workbook with many worksheets. I would like to search for
all
cells with a fill color of "yellow" and change to "No Fill". Is this
possible?

Thanks.