Tab color not updating (VBA), despite target cell doing so

[Quantonium]

I'm trying to use VBA to get tabs/sheets to change color depending on the TRUE/FALSE status of cell A1 (A1 checks if there are any FALSE values in a column of TRUE/FALSE values that will show FALSE if a certain value in that row is greater than a certain other value in that row). A1 updates accordingly when i restart excel, or press CTRL-ALT-F9, but the only way to get the tab-color to update is by writing something manually in a random cell in the specific sheet.

I would like the VBA to somehow recognize the A1 value as FALSE, and update the color of the tab.

I've included pictures of the VBA, general setup of the sheet, and intended outcome. Note that I know very little about VBA, and mostly followed an online guide for this to work.



Thanks

 

[BSALV]

I suppose A1 is a formula, not a fixed text or number ?
How does that formula look like ?
If you know its' sources, you can tackle those.

Or you can use other events like selecting that worksheet, ....

 

[Quantonium]

I suppose A1 is a formula, not a fixed text or number ?
How does that formula look like ?
If you know its' sources, you can tackle those.

Or you can use other events like selecting that worksheet, ....

Yes, A1 is a formula. I've included it in the top of the pictures. It simply checks if there are any FALSE values in the same column. The formulas below A1 are "=NOT(E# >= F#)" (where # is the row).

 

[BSALV]

if you don't know the structure of your workbook, i can only give you general advice.
The code here below is for the module "ThisWorkbook" , so you don't have to repeat that in the sheetmodules.

option 1 : As you open the workbook, the event "Workbook_open" is triggered and 'll loop through all worksheets and adjust the tabcolor

option 2 : when you go to another sheet (=activate), the event "Workbook_SheetActivate" is triggered and 'll check the tag of that activated worksheet

option 3 : when you change something in a workbook, the event "Workbook_SheetChange" is triggered and you can check any tab of any worksheet.

So now you can choose or describe better your case.

Option Compare Text                                             ' this module is not case sensitive

Private Sub Workbook_Open()

     Dim sh    As Worksheet
     For Each sh In ThisWorkbook.Worksheets                     'while opening check every worksheet
          Tab_color sh
     Next

End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
     Tab_color sh                                               'everytime you activate another worksheet, the tab of that sheet is updated
End Sub

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
     Select Case sh.Name                                        'in what sheet
          Case "BIOS2910", "KJM1140": If Not Intersect(Target, sh.Range("E:F")) Is Nothing Then Tab_color sh     'if something changed in those cells, check tabcolor
        Case "KJM3400": If Not Intersect(Target, sh.Range("A123:Z200")) Is Nothing Then Tab_color Sheets("BIOS2910")     'change in another sheet then the tab

     End Select
End Sub


Sub Tab_color(sh As Worksheet)
     r = Application.Match(sh.Name, Array("KJM3400", "BIOS2910", "KJM1140"), 0) 'only for this sheets
     If IsNumeric(r) Then sh.Tab.ColorIndex = IIf(StrComp(sh.Range("A1").Value, "False", vbTextCompare) = 0, 53, 10)
End Sub

 

[Quantonium]

Thanks a bunch! Appreciate it.
I chose a combination of option 1 and 2, such that it always checks all sheets when opening excel, and also checks all sheets whenever switching to/from any of the sheets.

Have a nice day!