How to use VBA code to colour tabs based on tab/text number

< Back to Search results

 Category: [Excel]  Demo Available 

How to use VBA code to colour tabs based on tab/text number




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.






Sub TabNameV2()
' Names each newly created tab with the SCPS name
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

  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
            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.


stars (0 Reviews)