VBA tab color change to color of cell in range

[Angel_Morrefni]

Hello Forum,

I have a workbook with alot of sheets. To easily check if any inspection is due i want my tab color to represent the most critical status to be shown.
I tried piecing together some code but nothing really worked for me.


In my column of inspection due dates i need to search for either red or yellow cells.
If there are any red (or yellow) cells in that column i need the tab color to change to the according color. Red has priority.
The cell color comes from conditional formatting.


Alternatively i considered a separate sheet which shows all the sheet-names in a column.
Each sheet-name should have the according color of the most critical status.

I hope this information is sufficient.

Kind Regards

 

[offthelip]

Welcome to the forum.
You haven't specified which column you are checking so I assume the range was A1 to A9 just for testing, change this to whatever you want. I assume it is the same ragne on every sheet

Sub test()
Dim cl As Range
red = 13551615
yellow = 10284031

For i = 1 To Worksheets.Count
    With Worksheets(i)
    tred = False
    tyellow = False
    For Each cl In .Range("a1:a9")
    tt = cl.DisplayFormat.Interior.Color
    If tt = red Then
     tred = True
     Exit For
    End If
    If tt = yellow Then
     tyellow = True
    End If
    
    Next cl
    
    If tred Then
     .Tab.Color = vbRed
    Else
     If tyellow Then
     .Tab.Color = vbYellow
     Else
     .Tab.Color = vbWhite
     End If
    End If
    End With
Next i

End Sub