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

jpitt916

New Member
Joined
Nov 22, 2013
Messages
12
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.

Code:
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi jpitt916,

Give this code a go; paste it in the "ThisWorkbook" module, it's activated when the workbook is opened so you'll have to close it and open it again.

Code:
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").Interior.ColorIndex = 3 Then
            ws.Tab.ColorIndex = 3   ' Red
            GoTo NxtSht
        End If
    Next i
   
   For i = 3 To 40
        If ws.Cells(i, "F").Interior.ColorIndex = 6 Then
            ws.Tab.ColorIndex = 6   ' Yellow
            GoTo NxtSht
        End If
    Next i
    
NxtSht:
Next ws
    
End Sub

Cheers,
Alan.
 
Upvote 0
Thanks Alan,

I feel like that's closer to the solution, it works whenever I re-open the file. However, it only turns the tab color as the first line specifies. I ran it without the first tab color index line and nothing happened at all. No errors popped up, however.

I forgot to mention this is Excel 2013.

FWIW, the cells in column F contain =E4-TODAY()
Column E4 is a due date

The conditional formatting is something like this:
If the cell contains -356 to 6, fill is red.
If the cell contains 7 to 15, fill is yellow.
If the cell contains 16 to 365, fill is green.
 
Last edited:
Upvote 0
Are you sure you are using colour index to change the colour of the cells? or are you just using the fill button?
 
Upvote 0
To answer better I used the standard conditional format dropdown in excel, not VBA.
 
Last edited:
Upvote 0
ok so if your just using the fill button and the standard red,orange and yellow fills, use this: Changes in red

Code:
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").Interior.[COLOR=#ff0000]Color = 255[/COLOR] Then
            ws.Tab.ColorIndex = 3   ' Red
            GoTo NxtSht
        End If
    Next i
   
   For i = 3 To 40
        If ws.Cells(i, "F").Interior.[COLOR=#ff0000]Color = 65535[/COLOR] Then
            ws.Tab.ColorIndex = 6   ' Yellow
            GoTo NxtSht
        End If
    Next i
    
NxtSht:
Next ws
    
End Sub


**ignor this I didn't see your edits
 
Last edited:
Upvote 0
Alan_P, yes sorry for that late edit, I had hoped to edit it in time.

Snakehips, I think you are hitting the nail on the head, I went ahead and made the edits Alan_P suggested, then manually selected the color for a cell as red, and sure enough the tab color changed to red.
 
Upvote 0
Thanks Alan,

I feel like that's closer to the solution, it works whenever I re-open the file. However, it only turns the tab color as the first line specifies. I ran it without the first tab color index line and nothing happened at all. No errors popped up, however.

I forgot to mention this is Excel 2013.

FWIW, the cells in column F contain =E4-TODAY()
Column E4 is a due date

The conditional formatting is something like this:
If the cell contains -356 to 6, fill is red.
If the cell contains 7 to 15, fill is yellow.
If the cell contains 16 to 365, fill is green.

Looking at the links Snakehips sent, it seems trying to find the colour of conditional formatting is (for want of a better expression) a ball ache!

So I think it's best we just ignore the "colours" altogether and build your conditions into the macro... can you send the exact conditions please?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top