Excel Macro: Removing Colors of All Sheets Tabs

This Excel VBA macro removes the colors all sheets tabs in an active workbook.

Macro Example

Sub RemoveSheetsTabsColors()

  Dim sh As Object

  For Each sh In Sheets
    sh.Tab.ColorIndex = xlColorIndexNone
  Next sh

End Sub

Description

Variable Declaration

Sub...

  Dim sh As Object

The 3rd line declares an object variable called sh which will refer to a Worksheet or Chart object.

Starts Looping

Sub...

  Dim sh As Object

  For Each sh In Sheets

The 5th line starts to loop through all the elements (=Worksheet or Chart) of a collection (=Sheets).

[Application.]Worksheets

The Application.Sheets property returns the Sheets collection object that represents all the sheets in the workbook.

obj-Excel-Application-Sheets

The Sheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-Sheets

Removing Tabs Colors

Sub...

  Dim sh As Worksheet

  For Each sh In Sheets
    sh.Tab.ColorIndex = xlColorIndexNone

The 6th line removes the color of a sheet tab.

Worksheet.Tab or Chart.Tab

    sh.Tab...

The Worksheet.Tab or Chart.Tab property returns the Tab object for a worksheet or chart.

obj-Excel-Worksheet-Tab

obj-Excel-Chart-Tab

Tab.ColorIndex

    sh.Tab.ColorIndex...

The Tab.ColorIndex property returns or sets a XlColorIndex value that represents the color of the specified worksheet or chart tab.

obj-Excel-Tab-ColorIndex

    sh.Tab.ColorIndex = xlColorIndexNone

In this macro, we remove the color of sheet tab by setting this property to the const: xlColorIndexNone .

obj-Excel-XLColorIndex-xlColorIndexNone

Loops Back

Sub...

  Dim sh As Object

  For Each sh In Sheets
    sh.Tab.ColorIndex = xlColorIndexNone
  Next sh

The 7th line loops back to get the next worksheet.

After every worksheet is evaluated, this macro ends.

Sub RemoveSheetsTabsColors()

  Dim sh As Object

  For Each sh In Sheets
    sh.Tab.ColorIndex = xlColorIndexNone
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-macro-remove-all-sheets-color.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007