When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give the same color to the tabs which are related to same function.
For example if you are preparing a dashboard for all the departments in an organization. All the worksheet tabs related finance can be highlighted in red, HR can be in Blue, etc. so with this report looks good and is easy to understand.
This Example will show you how to change the Color of Sheet tabs using Excel VBA. In the following Example we are changing the Sheet2 tabs color to Red. Lets have a look
Sub redColorSheetTab() Sheets("Sheet2").Tab.ColorIndex = 3 '3=Red, 4=green, 5=blue,6=yellow etc. End Sub
Here is another help code for programmers to change the color of Excel sheet tab. I have shown another way to achieve this:
'This function changes the tab color of a sheet
Sub ChangeSheetTabColor()
'
'Option 1 - using standard colors vbGreen, vbRed, vbBlack, vbYellow, vbBlue, vbWhite etc.
Sheet1.Tab.Color = vbGreen
'
'Option 2 - using RGB colors
Sheet1.Tab.Color = RGB(117, 117, 117)
'
End Sub
You may have noticed that in Option 2, I have used RGB function to change the sheet tab color. You may read this post to know more about how to get RGB codes of a color.
If you need to change the color of the tab you are currently viewing, you can use the following VBA macro code along with your desired RGB color code:
Sub ChangeTabColor()
'Objective: Change Selected Tab To Specific Color
ActiveSheet.Tab.Color = RGB(25, 25, 25)
End Sub
If you need to write a VBA loop to ensure all worksheet tabs have their color removed, you can use a macro similar to the below code:
Sub ClearAllTabColor()
'PURPOSE: Remove Tab Color from all Sheets
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Tab.Color = xlNone
Next sht
End Sub
To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.
We are currently offering our Excel VBA Course at discounted price. This courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…
How to send bulk emails from outlook using excel VBA? Have you ever felt the need of an Excel based VBA tool or code which can help you to draft Outlook emails in bulk by…
VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you here.
VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…
Working with huge data is always exciting and challenging. From 2007 version onward, Excel is supporting more than a million rows in each worksheet. One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates. In this article, we will be sharing 4 ways to delete duplicate records from your data.