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!
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 Facebook, Twitter, 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!