How To Remove Excel Tab Colors With VBA Code

By Chris Newman •  Updated: 09/17/14 •  3 min read
Remove Excel Tab Colors With VBA Code

Not So Simple When the Macro Recorder Tries to Play Tricks

Today I was writing a macro that automatically highlighted (re-colored yellow) worksheet tabs that I needed to manually check for various reasons.  Naturally, I wanted to write some VBA code that would remove the highlight color from the tabs after I had checked them all.  Off the top of my head, I was unsure of how to remove the color of a worksheet tab, so I did what I hope I’ve instilled in all of my readers to do when they don’t know how to code something in VBA; I used the Macro Recorder!

What The Macro Recorder Records

After recording myself removing the color from a tab, the following is what Excel wrote…

Sub Macro1()

' Macro1 Macro

With ActiveWorkbook.Sheets("Sheet1").Tab
  .ColorIndex = xlAutomatic
  .TintAndShade = 0
End With
End Sub

It looked simple enough and made reasonable sense to me, so I went ahead and tried to implement the code.  Unfortunately, I received a nasty VBE run-time error message while executing the code!  What the heck?!

Run-time error '9'

I guess this is an example of when the Macro Recorder doesn’t work 100% of the time.  If you have an idea of why this is happening or why Excel records the action of removing a tab color in this fashion PLEASE let me know in the comment’s section below!  I’m extremely curious.

The Proper Way To Remove A Tab’s Color

Through a little research on a few Excel forums I was able to figure out how to really remove the color of an Excel Worksheet tab.  Below are two examples of how you can remove the color.  One handles if you want to specify a particular worksheet name and the second will only change the currently  viewed (or active) tab.

Sub RemoveTabColor()
'PURPOSE: Remove tab color from worksheet

'Specific Tab
  ThisWorkbook.Worksheets("Sheet1").Tab.ColorIndex = xlColorIndexNone
'Active Tab
  ActiveSheet.Tab.ColorIndex = xlColorIndexNone
End Sub

Remove Color From All Tabs

Remove Excel Tab Colors With VBA Code

Another possible macro you may want to add to your Personal Macro collection might be to remove the color from all the worksheet tabs in a workbook. Below shows a loop you can add to cycle through all the worksheets in the active workbook and remove their respective tab’s color.

Sub RemoveTabColors()
'PURPOSE: Remove color from all spreadsheet tabs

Dim sht As Worksheet

'Loop Through All Worksheets in Active Workbook
  For Each sht In ActiveWorkbook.Worksheets
    'Remove Color
      sht.Tab.ColorIndex = xlColorIndexNone
  Next sht
End Sub

Now You’re All Set!

I’m assuming that most of you reading this page stumbled upon it after having a similar situation with the macro recorder’s code.  Hopefully this served as a quick solution for you and you’re already on to greater and better things.  If you have any questions at all concerning Excel tab colors that were not covered in this post please feel free to leave a comment below and I’ll try to write back as soon as I can!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!