How to use VBA code to colour tabs based on tab/text number
Category: [Excel] Demo Available
How to use VBA code to colour tabs based on tab/text number
Requirement:
The user requires a code that colour's tabs red if they contain a number >0 and colour's the other tabs that contain a number = 0 green. Example: Richard 1 = Red, Paul 0 = Green, Peter 5 = Red, Ozgrid 0 = green.
The user has3 tabs (all named) that I wish NOT to colour.
All of the tabs that the user would like coloured would have been created automatically by VBA form a pivot table.
Solution:
Code:
Sub TabNameV2()
' Names each newly created tab with the SCPS name
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Worksheets("Summary").Activate
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("Z2")) > 0 Then
ws.Name = Replace(ws.Range("Z2").Value, "/", "-")
' Adjust Tab Color : 0 = Green Else Red
If InStr(1, ws.Name, "0") > 0 Then
ws.Tab.Color = vbGreen
Else
ws.Tab.Color = vbRed
End If
End If
On Error GoTo 0
If ws.Name <> Replace(ws.Range("Z2").Value, "/", "-") Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to insert a row at the top of a table |
| How to skip VBA Code if table filter returns nothing |
| How to set dynamic dates for pivot table grouping |
| How to marge tabs from different excel macro xlsm files into one file |
| How to use VBA code to list specific tab names |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.