Monday, March 20, 2017

VBA code to change tab color in excel workbook!!!

Tabs are basic elements of each workbook in excel application. In excel, tabs can be colored with different colors. Using color coding to each tab, user can specify their use based on the data containing each tab. However, color can be applied in excel tab using excel macro. let's see how tabs can be colored using excel macro.


  
Here is the VBA code to change color for each tab in a workbook: 

Option Explicit

Sub Change_Tab_Color()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim i As Integer
Dim sht As Worksheet
    
'For loop to change color of each tab
  For i = 1 To ActiveWorkbook.Sheets.Count
    Sheets(i).Select
    Set sht = ActiveSheet
    'Change color index values 21 to your required color. refer color index values from table given at end of this article
    sht.Tab.ColorIndex = 21
  Next i

'Activate Sheet1
Sheets(1).Select

MsgBox "Color changed for all tabs.", vbInformation, "Change Tab Color"
End Sub

Refer below table for color index value for few colors:



No comments: