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!

 

[Snakehips]

j,

If you want to give yourself some more flexibility with your schedule then try this......
Select your merged cell A2:F2 and un-merge.
With those cells still selected goto Format Cells >> Alignment Tab >> Text alignment Horizontal and select 'Center Across Selection' >> ok
Then with A2:F2 selected, use format paster to copy that format to all your other merged rows.

Then go to Conditional Formatting.

Delete 6 out of the 9 conditions so that you have just i for each colour.

Edit red to use a Custom Formula and use =AND($F4>-366,$F4<4,isnumber($E4))
Edit the Applies To range to, say, $F$4:$F$200

For the other two conditions, just alter the Applies To range.

Then you should be ok to insert or delete rows from your schedule at will.

Re the tab colouring code, you either use my version or edit Alan's to extend the range beyond his current 40.

Hope that helps.

 

[Alan_P]

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.

Hi Tony,

I think your code is pretty elegant in it's simplicity! I need to learn a bit more about using 'select case'...

I just have one small question; I've noticed a lot of people use the WorkSheet_Open/Change/SelectionChange sub to call macros from a module rather than putting the code in the sub itself. I'm just wondering if there is a particular reason for this?

Cheers,
Alan.

 

[Snakehips]

Hi Alan,

Select Case is a really useful construct. If ever you are faced with multiple If's it's worth looking at Select Case.

It also handles Or's nicely, as you can have more than one qualifying value in a case.

eg...

Sub Test()
For t = 1 To 6
Select Case t
Case 1, 6
Tst = "1 or 6"
Case 2
Tst = "Single value 2 "
Case 3 To 5
Tst = "between 3 - 5 inclusive"
End Select
MsgBox Tst
Next t
End Sub

Re the calling of macros from the module. In most instances I would probably code directly in the event procedures but here it appeared to me that j might want to refresh the tab colours more often than just on Worksheet Open. Maybe with Sheet selection or Worksheet change. In that case it is better to have the one macro that can be called by several events rather than duplicate the macro coding within each of several event codes. There are other instances where it makes sense to have certain discrete routines that can be called as and when required by other parts of your code. Breaking long code down in this way can often make it more understandable. Coding and calling with parameters is also extremely useful at times.

Hope that helps.

 

[Alan_P]

Thanks Tony! Yes that helps a lot!... I'm off to find somthing that I can use 'select case ' in

Ah yes I see your point about calling the same macro several times, seems obvious now that you've told me ray: