Find/replace format?

[Guest]

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 Ogilvy]

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.

 

[Guest]

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]

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

 

[Guest]

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