Maybe you specified too much and it didn't match your data:

Option Explicit
Sub change_background_color()
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
With Application.FindFormat.Interior
.ColorIndex = 8
End With
With Application.ReplaceFormat.Interior
.ColorIndex = 15
End With
Range("a2:L27").Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True
End Sub

I got rid of the selection and the .pattern and .patterncolorindex as well as
using .findformat.clear and .replaceformat.clear.

And it worked fine for me.


eugene wrote:

Tried this (xl2003) but for some reason it did not work.

The following macro was created:

Sub change_background_color()
Range("A2:L27").Select
With Application.FindFormat.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Application.ReplaceFormat.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

When I changed the color back to 8 and tried to run it again, nothing
doing. It selected the range but did not change the color.

--
eugene

"Dave Peterson" wrote:

xl2002+ offers the option to change formats via Edit|Replace.

If you're not using xl2002+, then looping is your answer.

If you are using xl2002+, you can record a macro when you do it manually.



eugene wrote:

Hi,

I have a random group of cells in a range whose background color is blue. I
would like to change the background color of all these cells. I know that
this can easily be done with a loop that searches for colored cells and makes
the change when found.

I am wondering, though, if there is any neater way to do this, maybe using
a range command or something similar - i.e. for all cells in myRange that
are colored blue, change the color to red. Any thoughts if this is possible
and how it might be done.

--
eugene

--

Dave Peterson


--

Dave Peterson