How To 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...
' Macro1 Macro
.ColorIndex = xlAutomatic
.TintAndShade = 0
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?!
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.
'PURPOSE: Remove tab color from worksheet
ThisWorkbook.Worksheets("Sheet1").Tab.ColorIndex = xlColorIndexNone
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
Remove Color From All Tabs
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.
'PURPOSE: Remove color from all spreadsheet tabs
Dim sht As Worksheet
'Loop Through All Worksheets in Active Workbook
For Each sht In ActiveWorkbook.Worksheets
sht.Tab.ColorIndex = xlColorIndexNone
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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
3 Ways To Alternate Row Colors in Excel [Guide]
How to Alternate Shading Row Colors (Banded Rows) While Microsoft Excel does not have a dedicated row banding button to...
Microsoft Office 2023 Theme Color Update
Introducing the New 2023 Microsoft Office Color Theme Over the years, we've seen the Microsoft Office suite undergo a plethora...
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.