Colouring Tabs dependant on True / False condition

[Grimace]

Good morning,
I have "borrowed" the below code from a previous solved post, however am unsure how to adapt it.

Please Login or Register  to view this content.

What I am looking for is to have the tabs in a workbook highlight if dates on them are prior to todays date (ie a number of rows in each sheet will have a date in column F).
I thought of using a True / False cell as a reference point, where if all dates are past today() then it will be up to date and give a true Value. If this is the case I would like the tab to be a light Green colour to indicate it is up to date.
If any of the dates are prior to today, it will return false. I am then wanting to colour the tab as a light Red to indicate that Tab needs to be updated.

I can get to the True / False condition working correctly (eg into cell B2), however cannot get the Tab to change colour dependant on this cell.

Any assistance would be greatly appreciated.

Thanks in advance

[DonkeyOte]

The code you referenced wouldn't actually work in it's own right given the test: "elective class:" is in the wrong case.

Based on your own requirements perhaps:

Please Login or Register  to view this content.

[Grimace]

Hi DonkeyOte,

Please see attached sheet as an example.
I have added the code as per your reply to the worksheet tab, however there does not seem to be any change?
I apologise in advance as the VBA side of things is totally new to me, and I am not sure where to even start looking to trouble shoot.

I am looking to have the worksheet tab at the bottom Green if all of the dates in column F are in the future, or change to red if any are in the past or equal to today.
Darren

[DonkeyOte]

If I run the code all three tabs are changed to Green.

If you have tabs to be excluded it would be an idea to post back with a logical rule you can apply - ie any tab missing T&T header in F1 should be ignored etc...

[Grimace]

Hi DonkeyOte

When you say "run the code" what are you referring to?
I have entered the code into the "View Code" by right-clicking on the Tab name and then pasting it into the screen, then closing that window.

Is there something as basic as hitting a button that I am missing?

Also, yes this worksheet will be a part of a larger workbook, I was hoping to adapt the code once I have it right to suit the other sheets, rather than you having to do the lot for me.

If you are able to advise how I can include the line you mention (ie for any tab with T&T in F1) that would be great.

[DonkeyOte]

Unless the code you create is applied to an "event" then you must manually invoke the routine - you can do this by:

a) [native XL] ALT + F8 -> choose Macro -> press Run

b) [native XL] ALT + F8 -> choose Macro -> assign keyboard shortcut and use the shortcut to invoke the routine

c) go into VBEditor and run the code

d) [native XL] create a Forms Button, place this on a sheet and assign this to the Macro

NOTE: given the routine is explicitly set as Private (rather than Public) only option c will be available to you presently.

For more info. on what constitute an "Event" in Excel VBA see: http://www.cpearson.com/EXCEL/Events.aspx

If you are able to advise how I can include the line you mention (ie for any tab with T&T in F1) that would be great.

Please Login or Register  to view this content.

Note: further to earlier comments in the above I changed Private to Public (if you use Sub only [ie no Private/Public] then VBA assumes Public by default)

One final note - unless the code relates specifically (and only) to a given Sheet object I would suggest you store the code in a Module (VBE - Insert -> Module).

[Grimace]

Thank you DonekyOte, really appreciate all of the help you are providing me.

Is there a way for me to set it up to run this code upon the workbook being opened ?
(ie it update as the sheet is opened, so all of the tab colours are an indication of the status once it is opened)

[DonkeyOte]

Either

a) cut the code from it's present location and move it to ThisWorkbook object in VBE.

Once done change header from:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

or

b) if you prefer having the ability to run both from Open event and/or manually (as and when desired) leave in present location but add the below to ThisWorkbook:

Please Login or Register  to view this content.

the above will invoke the routine on open but the same routine can also be invoked manually as before if so desired.