How To Remove Excel Tab Colors With VBA Code

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': Subscript Out Of Range

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!

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!