Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2019 series. Have you ever needed to color all of your worksheets tabs at the same time, but had to color them one by one?. Well, this simple Excel macro will color all of your worksheet tabs at once with a different color. Excel has in excess of 16 million colors available. Each color has its own index value. Excel 2007 and over supports over 16 million colors. So, we have a few to choose from.

This really is a simple bit of code so let’s jump right on in.

Preparing To Write The Code.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   

You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

As you can see this code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW)

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start off the macro by inserting a New Module.  So, this is done by selecting the Personal.xlsb workbook, then Insert Module. 

Type Sub then the name of your macro.  In this example, I have called it simply Sub TabColors.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub TabColors()
End Sub
[/stextbox]

Declaring Variables.

We need to declare two variables in this macro. This simply means Excel creates a memory container for the values. In this code one is to store a reference to different worksheets, and the second is to store a reference to the colors.  These can be seen in the code below.  The IndexColor variable I have declared as long as it is a number, and can be very large.

[stextbox id=’info’]
Dim WS as Worksheet

Dim IndexColor as Long

[/stextbox]

Assign A Number To The IndexColor

The next step in the code to to assign a number to the IndexColor variable.  This is the first color that I will assign to the first sheet, as I color the next sheet along the number will increment so a new color is used on each Excel worksheet tab.  I will start with the index color at 1.  This is the first color Index I will use as a base.  Remeber we have over 16 million. 

[stextbox id=’info’]

IndexColor = 1

[/stextbox]

 

Use For Each Looping – Color Excel Worksheet Tabs.

So, in order to color each sheet I need to repeat the instructions each time with a new color.  We so this using the For Each looping code and increment the value of IndexColor by 2000.

[stextbox id=’info’]

For Each WS In Worksheets
WS.Tab.Color = IndexColor
IndexColor = IndexColor + 50000

Next WS

[/stextbox]

 

Ending The Macro.

The code ends once all of the worksheet tabs have been colored. This piece of code, the End Sub was already generated automatically when we began our code at the start of this process in step 1 above.

[stextbox id=’info’]
End Sub
[/stextbox]

So that’s it. A really useful Excel macro to color your Excel worksheet tabs all at once.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

Macro Mondays Blog Posts.

 

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  Need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.