Conditional Formatting on tab colours in 2003.

[Wulluby]

Hi,


I have a very simplified table with randomised data on:

https://skydrive.live.com/redir.aspx?cid=b81cfd1b7bf7bb2c&resid=B81CFD1B7BF7BB2C!213&parid=B81CFD1B7BF7BB2C!195


Is it possible to have excel change the tab colour for the Maternity sheet depending on date and data in Last Working Day, Return to Office and Status?


The idea is that this doc is used to track maternity leavers, accounts need to be locked down on the day they break for maternity leave and re-opened the day before they return.


There is conditional formatting on the Columns so that if Last working day is today or before today and there is no text in the Status Column then that cell will display red indicating work is to be done. The return to office has a similar formatting.


I would like to translate that to the tab colour so that if there is an outstanding Maternity leaver with an empty status then the tab is Red and if there is a Return to work job to be done it will display yellow for a number of days before the return date unless the status is showing as ‘Returned’


Thanks in advance.

 

[SirJB7]

Hi, Wulluby!

As far as I know, you can't conditionally format sheet tab color. The VBA code for doing that is (assuming red):

-----

[pre]

Sheets("Hoja1").Select
With ActiveWorkbook.Sheets("Hoja1").Tab
.Color = 255
.TintAndShade = 0
End With

[/pre]
-----

You'll have to adapt your code to the desired criteria and include previous snippet.

Regards!

 

[Wulluby]

Hi JB,


Thanks for that. 3 weeks ago I did not have one formula in my inventory so trying to get a grasp on this is a bit far. I thought I could do it with an adaptation of


Private Sub Worksheet_Calculate()


If Range("D6").Value = "" Then

Me.Tab.ColorIndex = -4142 ' No Color

Else Range("D6").Value <=TODAY() Then

Me.Tab.ColorIndex = 6 ' Yellow

End If


End Sub


using ElseIfs for the multiple arguments but I’ll maybe have to rethink the whole approach on how to get that sheet to alert me when work is required, or I could simply just check the sheet daily

 

[SirJB7]

Hi, Wulluby!

Your approach will work. Just please write down the conditions for yellow tabbing and red tabbing and I'll give you back the code updated. Please describe all valid combinations of LastWorkingDay, ReturnToOffice and Status columns.

Regards!

 

[Wulluby]

Thank you JB, much appreciated.


The combinations would be:


• If there is a date anywhere within the ‘Last Working Day’ column that equals today or is before today then the Tab colour should be Red, unless what is in the Status Column for that row show’s either ‘Locked’ or ‘Returned’. This would show at a glance that work needs done today and once the Status is filled out to show that the work is complete then the tab colour will switch back to regular no fill.


• If there is a date in the ‘Return to Office’ column that is within 5 working days in advance of today and the ‘Status’ column for that row does not have a value of ‘Returned’ then the tab colour should show as yellow. This will show at a glance that an expected maternity leaver is due back within the next week and action is required, once the operator has completed the work and filled out the ‘Status’ with returned then the tab colour will return to regular no fill.


An example of the document is still up on;

<https://skydrive.live.com/redir.aspx?cid=b81cfd1b7bf7bb2c&resid=B81CFD1B7BF7BB2C!213&parid=B81CFD1B7BF7BB2C!195>

 

[SirJB7]

Hi, Wulluby!


Check this file:

http://www.4shared.com/office/nTfQAr4f/Maternity_leavers_-_Conditiona.html


Red for 1), yellow for 2) and green for any ot those.

Highlights cells and colours tab.


Just advise if anything wrong.


Regards!

 

[SirJB7]

Hi, Wulluby!

Sorry, haven't read "working days". Here's the update:

http://www.4shared.com/office/6eltxHcf/Maternity_leavers_-_Conditiona.html

Regards!

 

[Wulluby]

Thanks JB,


I had a quick look at it last night and it looked good, seemed to do exactly what was needed on the tab though the colour formatting on the cells within seemed to have changed. Not sure if that was the code or not.


Meant to have a look at it closer at work today but I'm unable to download, (I knew I should have emailed it to myself last night ). Bit weird, work didn't filter out that site before but hey ho no biggie, I have it at home.


Will have a look at it over the weekend, I take it I can just copy that one page of code and apply it to the master sheet once I have created the appropriately named ranges?


Much appreciate your input on this, it looked great.

 

[SirJB7]

Hi, Wulluby!

Yes, if you move/copy the Maternity sheet to another workbook and you copy/paste the VBA code, then everything is supposed to work as with the file I uploaded. You won't have to adjust any range names unless you've got more columns (and if you only insert you'd neither have to), and the ranges are defined on the whole columns, so number of entries wouldn't be a problem neither.

Just advise if any trouble.

Regards!

PS: I deleted many redundant previously defined conditional formats, because they stated the conditions but had no applied format assigned, and I was going to override them all with the new ones, so I considered deleting them so as to get a cleaner sheet.

 

[Wulluby]

JB!


That looks great. Very much appreciated.


Sorry for the delay, I was getting errors on it on the machine at work, 2k3 doesn't like


If Application.WorksheetFunction.NetworkDays(Int(Now()), CDate(rngRTO.Cells(I, 1).Value)) <= 5 And _

rngSta.Cells(I, 1).Value <> "Returned" Then


I think.


But I see it working on 2010 and it looks great. Thanks.

 

[SirJB7]

Hi, Wulluby!

I don't remember if that worked in 2003, it yes does in 2007/2010.

Regards!