Find/replace format?
 

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.

Find/replace format?
 

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.

Find/replace format?
 

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.

Find/replace format?
 

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.

Find/replace format?
 

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.