How to Change Sheet Tab Color in Excel VBA

In this blog post, you will learn how to change the Sheet tab color in Excel VBA.

Suppose that we have some sheet tabs in our Excel sheet and need to change our sheet tab color.

For example, see the image below:-

There are 4 sheets in the above image.

Now with the help of VBA macros, we will change the color of these sheets.

We can change the color of multiple sheets here but here we will change the color sheet “Main”.

Here we can change the sheet tab color by two methods.

Below are both methods.

Method 1:- Changing Sheet Tab Color by VBA Standard Color Name

Method 2:- Changing Sheet Tab Color by VBA Color Index Number

Changing Sheet Tab Color by VBA Standard Color Name

In this method, we will change the sheet tab color by VBA Standard Color Name.

There are 8 standard colors in Excel VBA and these colors are as follows:-

vbBlack

vbWhite

vbBlue

vbRed

vbGreen

vbCyan

vbMagenta

vbYellow

Here we will use some of these color code names to change our “Sheet Tab Color”.

Write these codes in a new module and execute by pressing “F5” a single time or by pressing “F8” for step by step processing until the “End Sub”.

Sub Sheet_Tab_Color_By_Color_Name() Sheets("New Data").Tab.Color = vbRed Sheets("New Data").Tab.Color = vbBlue Sheets("New Data").Tab.Color = vbGreen Sheets("New Data").Tab.Color = vbYellow End Sub

See the result in the below image:-

Changing Sheet Tab Color by VBA Color Index Number

This is the second method to change the “Sheet Tab Color”.

Here, we will use the color index number to change the sheet tab color.

In this method, we can use the number from 1 to 56.

Below is the macro coding for changing the sheet tab color by using the color index number.

 

Sub Sheet_Tab_Color_By_Index_number() Sheets("New Data").Tab.ColorIndex = 1 Sheets("New Data").Tab.ColorIndex = 2 Sheets("New Data").Tab.ColorIndex = 5 Sheets("New Data").Tab.ColorIndex = 7 Sheets("New Data").Tab.ColorIndex = 9 End Sub

With the help of the above VBA coding, we can see the result in below image.

In this method, we can use the more color index number to change the sheet tab color.

I hope you find this tutorial useful.

Please feel free to put your comments and suggestion in the below comment box.

Regards

Narendra 

Related Post

How to Change or Rename the Sheet Name in Excel VBA

What is Current Region Property in VBA

How to Move Sheets in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Font Color in Excel VBA