If cell A1 in any sheet except "Test_Template" in the workbook = "FAIL" then the sheets tab turns red VBA

[Tom98]

Hi, I have a workbook with multiple sheets in it. I want to be able to include the function that if Cell A1 in any sheet EXCEPT sheet "Test_Template" equals "FAIL" then that sheet tab colour changes to red. Any help with how to go about adding this in would be great.

TIA

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Double click on This Workbook in upper left corner of window
Paste the code in the VBA edit window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  10/28/2020  5:59:03 AM  EDT
Dim ans As String
ans = ActiveSheet.Name
If ans <> "Test_Template" Then

    If Target.Address = Range("A1").Address Then
        If Target.Value = "FAIL" Then ActiveSheet.Tab.Color = vbRed
    End If
End If
End Sub

 

[My Aswer Is This]

Now if you want it to change back to Tab color white if any other value is entered in A1.
Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  10/28/2020  6:04:22 AM  EDT
Dim ans As String
ans = ActiveSheet.Name
If ans <> "Test_Template" Then

    If Target.Address = Range("A1").Address Then
        If Target.Value = "FAIL" Then
            ActiveSheet.Tab.Color = vbRed
        Else
        ActiveSheet.Tab.Color = vbWhite
        End If
    
    End If
End If
End Sub

 

[My Aswer Is This]

Now if you like to use Case. Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  10/28/2020  6:16:17 AM  EDT
'Using Case
Dim ans As String
ans = ActiveSheet.Name
If ans <> "Test_Template" Then

    If Target.Address = Range("A1").Address Then
        
        Select Case Range("A1").Value
            Case "FAIL": ActiveSheet.Tab.Color = vbRed
            Case Else: ActiveSheet.Tab.Color = vbWhite
        End Select
        
    End If
End If
End Sub

 

[My Aswer Is This]

I'm sorry I should have used:
So depending on which one you use use:ActiveSheet.Tab.ColorIndex = xlColorIndexNone

Case Else: ActiveSheet.Tab.ColorIndex = xlColorIndexNone

 

[Tom98]

Now if you like to use Case. Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  10/28/2020  6:16:17 AM  EDT
'Using Case
Dim ans As String
ans = ActiveSheet.Name
If ans <> "Test_Template" Then

    If Target.Address = Range("A1").Address Then
       
        Select Case Range("A1").Value
            Case "FAIL": ActiveSheet.Tab.Color = vbRed
            Case Else: ActiveSheet.Tab.Color = vbWhite
        End Select
       
    End If
End If
End Sub

Many thanks for your reply. It keeps coming up with the debugger and highlighting the "If Target.Address = Rangw("A1").Adress Then" line when I try to use this. I have the following with a couple of lines commented out as they don't seem to work, could you recommend any adjustments to what I have so far?

Sub Change_Tab_Colour()
'Change Tab color of any sheet that contains "FAIL" in cell A1, except Test_Template

For Each Worksheet In ThisWorkbook.Worksheets

'If ActiveSheet.Name = "Test_Template" Then
'Next Worksheet

If Worksheet.Range("A1") = "FAIL" Then

Worksheet.Tab.Color = 255

Else

Worksheet.Tab.ThemeColor = xlThemeColorDark2

End If


Next Worksheet


End Sub

Cheers

 

[My Aswer Is This]

My script had:
If Target.Address = Range("A1").Address Then
Why did you change it to:
If Target.Address = Rangw("A1").Adress
Should be Range not Rangw

And why are you changing around the entire script?

My script works when you enter a value in Range("A1")
Are you saying you want the script to run when you press a button?

 

[Tom98]

My script had:
If Target.Address = Range("A1").Address Then
Why did you change it to:
If Target.Address = Rangw("A1").Adress
Should be Range not Rangw

And why are you changing around the entire script?

My script works when you enter a value in Range("A1")
Are you saying you want the script to run when you press a button?

Sorry, it was a typo in my previous message. Yes, if it could run with a press of a button then that would be great. Cheers

 

[My Aswer Is This]

If you want to use a Button:
Put this script in your button:

Sub Check_A1_Range_For_Fail()
'Modified  10/28/2020  7:46:20 AM  EDT
Application.ScreenUpdating = False
Dim i As Long


For i = 1 To Sheets.Count
    
    If Sheets(i).Name <> "Test_Template" Then
        Select Case Sheets(i).Range("A1").Value
            Case "FAIL": Sheets(i).Tab.Color = vbRed
            Case Else: Sheets(i).Tab.ColorIndex = xlColorIndexNone
        End Select
    End If
Next

Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If the cell value does not say FAIL I have it removing Tab color.
If you do not want that then remove this line of code:
Case Else: Sheets(i).Tab.ColorIndex = xlColorIndexNone