How to replace the background color of all cells from light green to light blue?

[Claudia d'Amato]

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

 

[Susan]

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

 

[Susan]

TYPO ALERT


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

should be

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

susan

 

[Mike H]

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

 

[Rick Rothstein]

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

 

[Gord Dibben]

Select all cells the Edit>Find>Options>Format>Format

Pick the light green color from the Patterns Tab and Ok>Find All.

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

On main menu, Format>Cells>Pattern.......pick light blue and OK.


Gord Dibben MS Excel MVP