Hello Excellers and welcome back to another #Excel #FormulaFriday blog post in my 2020 Excel series. Today I will show you how to conditionally format an Excel worksheet tab. This is useful if you automatically want to colour a worksheet tab once a cell has changed or a negative or positive number has been achieved.
The list of uses or the macro is really endless. This macro is based on the worksheet change event which will automatically trigger when a cell or a group of cells change.
Preparing To Write The Macro To Change The Worksheet Tab.
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.
How Does The Macro Work?.
This macro is a really simple one. Using the Worksheet_Change Event which occurs when cells on the worksheet are changed by the user or by an external link. in this example, I will be changing the colour of the worksheet tab. The syntax of the event is as follows.
expression.Change (Target)
expression A variable that represents a Worksheet object.
Where
Target – this is a required argument which is the changing range. It can be a cell or multiple cells.
Starting The Macro.
As this code deals with a worksheet change, then it makes sense that the code needs to be not in a module as in a lot of VBA code, but within the code of the worksheet itself. To enter the code into the relevant worksheet:
- Simply double click on the worksheet in the VB Project Explorer.
- In the left-hand drop-down box select Worksheet
- In the right-hand drop-down box select Change
Excel starts the code off for us, and also enters the End Sub command at this stage too. All we need to do is write the rest of the code between these two lines.
[stextbox id=’info’]
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
[/stextbox]
Using The IF THEN ELSE Statement To Change The Worksheet Tab.
In this example code, we will apply this logic The logic behind this is is if our target cell is equal to 100 then the worksheet tab will be coloured GREEN, else if it does not then the worksheet tab will be coloured WHITE. in this way, we can conditionally format our worksheet tab.
The IF-THEN-ELSE statement evaluates the conditions in the order listed. It will execute the corresponding code when a condition is found to be true. The first part of the statement will assess if the value in A1 if sheet 1 is equal to 100. If this is true then the sheet tab colour will be changed to green.
The second part of the statement will turn the colour of the worksheet tab white if the condition is false.
[stextbox id=’info’]
If Sheet1.Range(“A1”).Value = 100 Then
Sheet1.Tab.Color = vbGreen
Else
Sheet1.Tab.Color = vbWhite
End If
[/stextbox]
Ending The Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
