Tab Color based on Conditional Cell Color in a range of cells.

[jpitt916]

Ok, my goal is to change the Tab color based on the conditional color of a range of cells. If any cell in the range is red, the tab color is red; or if any cell in the range is yellow, the tab color is yellow, else tab color will be green. I got the code to work when checking one cell only, but when I extend the range it is behaving like ALL the cells in the range have to meet the criteria, when in reality I only need one cell to be red or yellow to change the tab color. I'm sure this would be easier if I actually knew what I was doing, but I am just piecing together stuff I've searched for....

So whenever I play it, it changes the tab color to whatever color I have in the "Else" line.

Also, I will have multiple worksheets all doing the same thing and I don't want to have to run the code manually for every worksheet, I'd just want to hit F9 (I think F9) and have it run the code for all tabs, or preferably it will run whenever the file is opened.

Private Sub Worksheet_Calculate()


    If Range("F3:F40").DisplayFormat.Interior.ColorIndex = 3 Then
        Me.Tab.ColorIndex = 3   ' Red
    End If
    If Range("F3:F40").DisplayFormat.Interior.ColorIndex = 6 Then
        Me.Tab.ColorIndex = 6   ' Yellow
    Else
        Me.Tab.ColorIndex = 45       ' Orange
    End If
    
End Sub

Thanks for your help!

 

[Alan_P]

Yes, I agree, that would be quite a pain to accomplish. I think that may be an easier solution.

My other thought was to assign a priority code 1, 2, or 3 via conditional formatting. Under the existing conditional formatting rules, I clicked on Format, and in the "Number" tab I highlighted "Custom", and then set it as a 1, 2, or 3.
Would this be easier than building a more robust macro?

I was thinking something like this, so the macro itself would check the conditions...

Private Sub Workbook_Open()


Dim ws As Worksheet
Dim i As Long


For Each ws In ActiveWorkbook.Worksheets


    ws.Tab.ColorIndex = 45       ' Orange
    
    For i = 3 To 40
        If ws.Cells(i, "F").Value > "-356" And ws.Cells(i, "F").Value < "7" Then
            ws.Tab.ColorIndex = 3   ' Red
            GoTo NxtSht
        End If
    Next i
   
   For i = 3 To 40
        If ws.Cells(i, "F").Value > "6" And ws.Cells(i, "F").Value < "16" Then
            ws.Tab.ColorIndex = 6   ' Yellow
            GoTo NxtSht
        End If
    Next i
    
NxtSht:
Next ws
    
End Sub

 

[jpitt916]

Ok, that makes sense. I can just use what you gave me there and adjust the conditions however I need them. I'll post up shortly if it works.

 

[jpitt916]

It seems to just turn the tab red.

What I actually have is -365 to 3, red; 4 to 7, yellow; 8 to 365, green. I'm trying to upload the file so you aren't shooting in the dark, so to speak.

I really appreciate your help.

 

[jpitt916]

Ok, here is the file:
https://drive.google.com/file/d/0B-fW4O5SBTAObU5tRTNMTUxCbjA/edit?usp=sharing

 

[Alan_P]

That does make it a lot easier

Never used google drive before either... makes it handy to share workbooks!

So I tested it out a bit and this seems to be working OK now

https://drive.google.com/file/d/0B867a8TcsKV6ZWtNSFVnTHdkZmM/edit?usp=sharing

Seems to be a lot of duplicated conditional formatting on there?

 

[Snakehips]

jp,

Here is code that should do your tab colour change.

Insert a Code Module into the vba project and paste it in there.

Sub Color_Tabs()
For Each ws In ActiveWorkbook.Worksheets
Select Case WorksheetFunction.Min(ws.Range("F:F"))
Case Is < 4
MyColour = 3  'red
Case Is < 8
MyColour = 6  'yellow
Case Is < 31
MyColour = 4   'green
End Select
ws.Tab.ColorIndex = MyColour
Next ws
End Sub

If you wish to call it on opening the workbook then use

Private Sub Workbook_Open()
Call Color_Tabs 
End Sub

Similarly, you can call it with e.g. the Workbook SheetChange event and or the Worksheet Change event.

Hope that helps.

 

[jpitt916]

That does make it a lot easier

Never used google drive before either... makes it handy to share workbooks!

So I tested it out a bit and this seems to be working OK now

https://drive.google.com/file/d/0B867a8TcsKV6ZWtNSFVnTHdkZmM/edit?usp=sharing

Seems to be a lot of duplicated conditional formatting on there?

There's a lot of duplication because of a merged cell separating each of the time period sections, so when I copied and pasted rows it automatically split up the rules.

 

[jpitt916]

Alan_P that works like a charm!!!

I can't thank both of you enough for your help! Thank you very much!!

 

[Snakehips]

You are welcome.

Just a note that Alan's code is currently specific to checking rows 3 to 40 and would need changing if you extend your data beyond that whereas my code would not.

 

[jpitt916]

Thanks Tony. I feel like adding the cell conditional formatting via VBA would probably be a better option than the conditional formatting I have now but I don't need to create more work when I have that part working the way it is now. I played with it a bit last night but decided it should just be left alone.