BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Remove Color of all Sheet's tabs in Microsoft Excel

This post contains VBA macro example on how to remove the color of sheet tabs using Microsoft Visual Basic for Applications in Microsoft Excel. We will loop through all worksheets using FOR-NEXT and will set the sheet's tab color to None. If you are new to the VBA macros in MS Excel, follow these instructions:
  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to 'Insert' in the Menu --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

Sub RemoveColorSheetsTabs()
     'This macro will remove color from sheet's tabs of the Active Workbook.
     Dim mySheet As Worksheet
     For Each mySheet In ActiveWorkbook.Sheets
          mySheet.Tab.ColorIndex = xlColorIndexNone
End Sub

How to use? To run these macros, simply press F5 while placing the cursor inside the macro code.
OR From MS Excel window, press 'Alt+F8' to select the macro and then hit the 'Run' button.
References For more information about the properties and Excel constants used inside the above macros, type the following text on a module sheet:
  • mySheet.Tab
  • Tab.ColorIndex
  • xlColorIndexNone
Highlight the property or constant about which you want more information, and then press F1.
Supported versions of MS Excel: APPLIES TO
  • Microsoft Office Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition

Anonymous said...

There is an error if the sheet is a graph sheet (activesheet.type = 1 or 3...)