Macro for changing background color of the cells

[rishuverma]

I will be extremely extremly thankful if anybody can help me .....

I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.


I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to red.....you can well imagine my condition.....my life would get shorten significantly while doing this.

Many Many Advance thanks for this.......

[VBA Noob]

rishuverma,

Please read forum rules below about titles.

You don't need code to select the cells....try

Ctrl + H > Find what format > choose format from a green cell > options > change from within sheet to workbook and put in the replace colour

VBA Noob

[StephenR]

This should work, but it may take a while...

Sub x()
Dim ws As Worksheet, rng As Range
For Each ws In Worksheets
    For Each rng In ws.UsedRange
        If rng.Interior.ColorIndex = 10 Then rng.Interior.ColorIndex = 3
    Next rng
Next ws
End Sub

EDIT: didn't know VBA Noob's suggestion.

[VBA Noob]

Here an example of Find and replace. Change colour index as required

Dim Wsht As Worksheet

With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
End With
    With Application.FindFormat.Interior
        .ColorIndex = 35
    End With
   
    Application.ReplaceFormat.Interior.ColorIndex = 19
    For Each Wsht In Worksheets
    Wsht.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
    Next
    
With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
End With

VBA Noob

[rishuverma]

I am extremly thankful to VBA Noob and Stephen.......all the three solutions are quite good and impressive.

Is it possible that by running macro once I can change the color in all the sheet of the workook.....if not than I have to run this 500 times.......

Well, all this is after affect of Lehman Bankruptcy ..u know its color was GREEN

[VBA Noob]

Both my methods worked for whole workbook

VBA Noob

[rishuverma]

Thanks a ton guys for the help.......