Macro to Change Cell Background Color From White to No Fill

**emcdemc** · 01-06-2009, 06:14 PM

Hi Everyone,

I am new here and have very limited VBA experience but extensive standard excel experience. We have some massive excel models in which my predecessor decided to format each cell's background as white as opposed to leaving the cells un-filled and hiding gridlines. Therefore the excel files are needlessly enormous. As such, because some columns and rows are other colors than white and it will take an inordinate amount of time to manually change every row and column, I need a macro that essentially says "change cells in this workbook that are formatted with a white background to have no fill".

It is probably a super simple macro to create but I am having no luck with my searches. I am probably using incorrect language.

Any help would be greatly appreciated. Thank you.

**VBA Noob** · 01-06-2009, 06:26 PM

Maybe

Sub ChangeCellColour()
Dim Wsht As Worksheet
Dim Rng As Range
Dim cl As Range
    For Each Wsht In Worksheets
        Set Rng = Wsht.UsedRange
            For Each cl In Rng
                With cl
                    If .Interior.ColorIndex = 2 Then
                        .Interior.ColorIndex = 0
                    End If
                End With
            Next cl
    Next Wsht
    
    Set Rng = Nothing
    Set cl = Nothing
        
End Sub

or using Find and replace via format (Same as pressing Ctrl + H > Options > change formats as required > click whole workbook)

With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear

    .FindFormat.Interior.ColorIndex = 2

    .ReplaceFormat.Interior.ColorIndex = xlNone
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End With

VBA Noob

**emcdemc** · 01-06-2009, 06:56 PM

Thank you! That works but it takes a very long time per worksheet.

[EDIT] Trying the second one, thanks.

**emcdemc** · 01-06-2009, 08:23 PM

Thanks, the second one seems to work but how do I get it to cycle through each sheet in the workbook? Here is what I have:

Sub White2Blank()
Dim Ws As Worksheet

For Each Ws In Worksheets
    Cells.Select
    Application.FindFormat.Clear
    Application.FindFormat.Interior.ColorIndex = 2
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = xlNone
    Selection.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
Next Ws
End Sub

**shg** · 01-06-2009, 08:36 PM

You were close. You can't select the cells without selecting the worksheet, but there's no need to do either.

Sub White2Blank()
    Dim wks     As Worksheet

    With Application
        .FindFormat.Clear
        .FindFormat.Interior.ColorIndex = 2
        .ReplaceFormat.Clear
        .ReplaceFormat.Interior.ColorIndex = xlNone
    End With

    For Each wks In Worksheets
        wks.Cells.Replace What:="", Replacement:="", _
                          LookAt:=xlWhole, MatchCase:=True, _
                          SearchFormat:=True, ReplaceFormat:=True
    Next wks
End Sub