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

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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

Knowing that it can't be done also saves a lot of time. Thanks for answering.

--
eugene


"Jim Thomlinson" wrote:

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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

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

--
HTH...

Jim Thomlinson


"eugene" wrote:

Knowing that it can't be done also saves a lot of time. Thanks for answering.

--
eugene


"Jim Thomlinson" wrote:

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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

I'll try it and get back if I encounter any problems.

Thanks again.
--
eugene


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"eugene" wrote:

Knowing that it can't be done also saves a lot of time. Thanks for answering.

--
eugene


"Jim Thomlinson" wrote:

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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

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.
--
eugene


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"eugene" wrote:

Knowing that it can't be done also saves a lot of time. Thanks for answering.

--
eugene


"Jim Thomlinson" wrote:

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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'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...
--
HTH...

Jim Thomlinson


"eugene" wrote:

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.
--
eugene


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"eugene" wrote:

Knowing that it can't be done also saves a lot of time. Thanks for answering.

--
eugene


"Jim Thomlinson" wrote:

There is no easy way. I am affraid you have to loop through all of the cells.
--
HTH...

Jim Thomlinson


"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

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

Looked at this. It may take a bit of work to do what I want. But it's an
excellent suggestion.

Thanks.

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

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

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

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.
--
eugene


"Dave Peterson" wrote:

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

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.)

eugene wrote:

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.
--
eugene

"Dave Peterson" wrote:

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


--

Dave Peterson