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…
Sub Macro1()
' Macro1 Macro
With ActiveWorkbook.Sheets("Sheet1").Tab
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End SubIt 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.
Sub RemoveTabColor()
'PURPOSE: Remove tab color from worksheet
'Specific Tab
ThisWorkbook.Worksheets("Sheet1").Tab.ColorIndex = xlColorIndexNone
'Active Tab
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
End SubRemove 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.
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 SubNow 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!
Keep Learning
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.