Assume I have a worksheet where some (but not all !!) cells have a background color of light green.

How can I change at once (!) the background color of all those cells to light blue?

All other cells should remain unchanged.

Claudia

try this macro - change the range to suit your range. NOTE: this
will not work if the cells that are green are green because of
conditional formatting!!!! this will only work if they have been
manually colored. if you have conditional formatting involved, you
will need something different.

'--------------------------------------------------------------------------
Sub change_color()

Dim myWS As Worksheet
Dim c As Range
Dim myRange As Range

Set myWS = ActiveSheet
Set myRange = ws.Range("a1:g500") '<--CHANGE RANGE

For Each c In myRange
If c.Interior.ColorIndex = 35 Then 'green
c.Interior.ColorIndex = 34 'blue
End If
Next c

End Sub
'------------------------------------------------------------------------

hope that helps!
:)
susan



On Nov 14, 1:54*pm, (Claudia d'Amato) wrote:
Assume I have a worksheet where some (but not all !!) cells have a background color of light green.

How can I change at once (!) the background color of all those cells to light blue?

All other cells should remain unchanged.

Claudia

TYPO ALERT


Set myRange = ws.Range("a1:g500") '<--CHANGE RANGE

should be

Set myRange = myWS.Range("a1:g500") '<--CHANGE RANGE

susan


On Nov 14, 2:02*pm, Susan wrote:
try this macro - change the range to suit your range. *NOTE: *this
will not work if the cells that are green are green because of
conditional formatting!!!! *this will only work if they have been
manually colored. *if you have conditional formatting involved, you
will need something different.

'--------------------------------------------------------------------------
Sub change_color()

Dim myWS As Worksheet
Dim c As Range
Dim myRange As Range

Set myWS = ActiveSheet
Set myRange = ws.Range("a1:g500") *'<--CHANGE RANGE

For Each c In myRange
* *If c.Interior.ColorIndex = 35 Then *'green
* * * c.Interior.ColorIndex = 34 *'blue
* *End If
Next c

End Sub
'------------------------------------------------------------------------

hope that helps!
:)
susan

On Nov 14, 1:54*pm, (Claudia d'Amato) wrote:



Assume I have a worksheet where some (but not all !!) cells have a background color of light green.

How can I change at once (!) the background color of all those cells to light blue?

All other cells should remain unchanged.

Claudia- Hide quoted text -

- Show quoted text -

Hi,

Right click your sheet tab, view code and paste this ij and run it

Sub swapem()
For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 35 Then
c.Interior.ColorIndex = 41
End If
Next
End Sub

Mike

"Claudia d'Amato" wrote:

Assume I have a worksheet where some (but not all !!) cells have a background color of light green.

How can I change at once (!) the background color of all those cells to light blue?

All other cells should remain unchanged.

Claudia

How did those cells get their light green color? Was it from a Conditional
Format or did you manually set their color.

--
Rick (MVP - Excel)


"Claudia d'Amato" wrote in message
...
Assume I have a worksheet where some (but not all !!) cells have a
background color of light green.

How can I change at once (!) the background color of all those cells to
light blue?

All other cells should remain unchanged.

Claudia

Select all cells the EditFindOptionsFormatFormat

Pick the light green color from the Patterns Tab and OkFind All.

Select top cell in the "found" dialog box. SHIFT + End and Downarrow

On main menu, FormatCellsPattern.......pick light blue and OK.


Gord Dibben MS Excel MVP

On 14 Nov 2008 18:54:25 GMT, (Claudia d'Amato)
wrote:

Assume I have a worksheet where some (but not all !!) cells have a background color of light green.

How can I change at once (!) the background color of all those cells to light blue?

All other cells should remain unchanged.

Claudia