How To Remove Excel Tab Colors With VBA

The VBA Function presented will return the number of colored tabs that were reset to default color or -1 to indicate that the workbook is protected. Tab colors cannot be modified, if Workbook structure is protected. To remove Workbook protection, use our Password Remover add-in!

Please note that this VBA function will reset tab colors in both worksheets and chart sheets, whether visible, hidden or veryHidden.

Option Explicit

Function RemoveTabColors(oWorkbook As Workbook) As Long

    'PURPOSE: Remove color from all spreadsheet tabs and count colored tabs found

    Dim oSheet As Object    ' worksheet or chart sheet
    Dim lResetTabs As Long  ' tab counter

    'Tab colors cannot be modified, if Workbook structure is protected
    If oWorkbook.ProtectStructure Then
        RemoveTabColors = -1
        Exit Function
    End If

    'Loop Through All Worksheets & Chart Sheets in Workbook
    'Hidden or very hidden sheet tabs will be modified as well

    For Each oSheet In oWorkbook.Sheets
        If oSheet.Tab.ColorIndex <> xlColorIndexNone Then
            oSheet.Tab.ColorIndex = xlColorIndexNone ' Remove Color
            lResetTabs = lResetTabs + 1
        End If
    Next oSheet

    RemoveTabColors = lResetTabs

End Function

Sub TestMyFunction()
    Debug.Print RemoveTabColors(ActiveWorkbook)
End Sub

Caution: Invalid Macro Recorder VBA Code

Needless to say that the Macro Recorder is a useful tool included in Office VBA, as it can record every task you perform in Excel. Next, you can execute the task over and over with the click of a button.

Unfortunately, there are a lot of things you cannot do with the Macro Recorder, as demonstrated below. Moreover, the Macro Recorder uses a lot more code than is required, which can slow program execution or produces invalid code that may break with a runtime error.

Excel's Macro recorder fails to record valid code to reset tab color!