Set all sheets' fill color to "No Fill" using VBA in excel

Question

How can I Set all sheets fill color to No fill using VBA in excel (all cells). Same as select all sheets, Select all cells (Control+A) and change fill color to No fill.

Caesura

Answer 1

Untested but should do the job:

Sub No_Fill_Wb()

Dim ws as worksheet

For each ws in Thisworkbook.Worksheets
    ws.Cells.Interior.ColorIndex = 0
Next ws

End Sub

Phlebotomy

Equivalent would also be ws.Cells.Interior.ColorIndex = xlNone – Atalya

Answer 2

I used "0" in my code (see below) working with Excel 2016 and it turned my columns black.

Master.Columns(Col_Start).Interior.Color = 0

Using "xlNone" per below, however, did work for me.

Master.Columns(Col_Start).Interior.Color = xlNone 

Plage

This is because when you set Range.Interior.Color, it expects a long corresponding to the RGB values. 0 -> #000000 which is black. Range.Interior.ColorIndex increments through the default color palette, for which 0 is No Fill – Kamalakamaria

Answer 3

xlNone doesn't work for BackgroundPatternColor but wdColorAutomatic does

currentCell.Shading.BackgroundPatternColor = wdColorAutomatic

Foreshorten

There is no Word tag in question, Excel only. – Mudd

Answer 4

Hey guys you can use this for selecting "No Fill" option "ThisWorkbook.Sheets(1).Interior.Color = xlNone" otherwise if you want to set any background color you can use this way "ThisWorkbook.Sheets(1).Interior.Color = vbgreen"

Bonacci