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.
Custom Calendar Control for MS Access MS Access by default provides inbuilt functionality to pick dates using calendar control; however it lacks few basic functionalities which makes selecting a date bit difficult. For example, if…
VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…
VBA Code to Convert MM.DD.YYYY To DD.MMM.YYYY in Excel In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a…
Excel Add-in helps you to extend the features of Excel application. Using Excel Add-in, you can perform custom actions in Excel such as formatting the data, doing complex calculations which are not possible through Excel formulas, Reading or Writing data in other Excel files and so many more actions.
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.
In this article we will learn about VBA code to get computer name. Excel VBA, or Visual Basic for Applications, is a programming language that can be used to automate tasks within the Microsoft Excel…