Change background color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Here is some fairly simple code to try if it helps... The function returns
all of the cells in the range supplied that are the colour specified.

Sub test()
Dim rngFound As Range

Set rngFound = GetColouredCells(ActiveSheet.Cells, 5, ActiveSheet)

rngFound.Interior.ColorIndex = 4

End Sub

Public Function GetColouredCells(ByVal Target As Range, ByVal ColourIndex As
Integer, Optional wks As Worksheet) As Range
Dim rng As Range
Dim rngReturnRange As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set Target = Intersect(Target, wks.UsedRange)
For Each rng In Target
If rng.Interior.ColorIndex = ColourIndex Then
If rngReturnRange Is Nothing Then
Set rngReturnRange = rng
Else
Set rngReturnRange = Union(rngReturnRange, rng)
End If
End If
Next rng
Set GetColouredCells = rngReturnRange
End Function
 
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.
 
Looked at this. It may take a bit of work to do what I want. But it's an
excellent suggestion.

Thanks.
 
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.
 
Jim,

Your macro worked perfectly. At first it looked a bit too complicated for my
liking. But now that I understand it and was able to modify it for my exact
use, it's perfect.

Thanks again.
 
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.

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.
Click to expand...
 
Dave's is a bit more efficient, so if this code ever gets to the point where
it is too slow then take another crack at the replace code. Trueth is that I
completely forgot about the replace since the developement that I do needs to
be portable back to machines running XL2000...
 
Jim,

Dave's does work. Your code was worthwhile anyway. I learned a few new
things.

Thanks.
 
Dave,

It worked for me as well. What I had was an exact copy of what "record
macro" produced. I have found that it often puts in many lines that are not
needed. I am just never sure what is and what is not essential. I guess I
should have tinkered a bit.
Thanks for the further help.
 
I like to copy that code and put it into another module.

Then I can tinker a lot. If, er, when I screw up and need a fresh copy, I don't
need to record again--I just steal a copy from that other module.

(You could use Notepad for that extra storage, too.)
Dave,

It worked for me as well. What I had was an exact copy of what "record
macro" produced. I have found that it often puts in many lines that are not
needed. I am just never sure what is and what is not essential. I guess I
should have tinkered a bit.
Thanks for the further help.
Click to expand...
 
Back
Top