Another Automatic tab color change question

[usas12gthr]

I have multiple, but not all, tabs that I need to have the tab color change when a certain condition is met in any of four cells. Cell C3, C4, C5, and C6 will turn red if the value is greater than the Qty purchased value of that type. Note - these "C" cells auto-calculate using CountIf on a chart that is below If any of the "C" tabs are red I need the sheet tab to go red as well. If none of the cells are red then the tab color does not change. I need this to change when the chart is updated. Thank you

 

[Crystalyzer]

I put the code in BOTH the Worksheet "Calculate" and Worksheet "Change" subs so you can choose which one to use or both. I'm not checking for the interior color of the cells. Rather I am checking the original condition which is if C > B for a given row in the Range C3:C6 then turn the worksheet tab red. If none of the values are > than the values in B then the worksheet tab color is removed.

Private Sub Worksheet_Calculate()
    Dim r As Range
    Dim c As Range
    Dim tabRed As Boolean
    
    tabRed = False
    
    Set r = Range("C3:C6")
    
    For Each c In r
        If c.Value > c.Offset(0, -1).Value Then
            ActiveSheet.Tab.Color = 255
            tabRed = True
        End If
    Next
    
    If Not tabRed Then ActiveSheet.Tab.Color = xlNone
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range
    Dim tabRed As Boolean
    
    tabRed = False
    
    Set r = Range("C3:C6")
    
    For Each c In r
        If c.Value > c.Offset(0, -1).Value Then
            ActiveSheet.Tab.Color = 255
            tabRed = True
        End If
    Next
    
    If Not tabRed Then ActiveSheet.Tab.Color = xlNone

End Sub

 

[usas12gthr]

I put the code in BOTH the Worksheet "Calculate" and Worksheet "Change" subs so you can choose which one to use or both. I'm not checking for the interior color of the cells. Rather I am checking the original condition which is if C > B for a given row in the Range C3:C6 then turn the worksheet tab red. If none of the values are > than the values in B then the worksheet tab color is removed.

Private Sub Worksheet_Calculate()
    Dim r As Range
    Dim c As Range
    Dim tabRed As Boolean
   
    tabRed = False
   
    Set r = Range("C3:C6")
   
    For Each c In r
        If c.Value > c.Offset(0, -1).Value Then
            ActiveSheet.Tab.Color = 255
            tabRed = True
        End If
    Next
   
    If Not tabRed Then ActiveSheet.Tab.Color = xlNone
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range
    Dim tabRed As Boolean
   
    tabRed = False
   
    Set r = Range("C3:C6")
   
    For Each c In r
        If c.Value > c.Offset(0, -1).Value Then
            ActiveSheet.Tab.Color = 255
            tabRed = True
        End If
    Next
   
    If Not tabRed Then ActiveSheet.Tab.Color = xlNone

End Sub

Thank you