using VB in excel

Ace Modder · 9 Oct 2006 at 21:35

Hi guys

I have a spreadsheet with dates. I want to run a program to highlight todays date in red, yesterdays in yellow, day before in blue etc etc.

How can I do this automatically when the spreadsheet is opened?

Tomsk · 9 Oct 2006 at 22:43

You should be able to do it using conditional formatting.

suarve · 9 Oct 2006 at 23:38

Nar theres a bit of code you you put in the vb editor, really simple. Although its late now, ill try to log on tomorrow from work and put it in for you. But if its a really small and simple spd/sht id use conditional formatting as it takes less than 1 minute to set up.

Ace Modder · 10 Oct 2006 at 07:14

im a bit of a noob, when it comes to conditional formatting.

the spreadsheet is here....http://www.mhz.pwp.blueyonder.co.uk/jl.xls

There are varius tabs on the spreadsheet, all of which need to have the dates colour coded.

Ace Modder · 11 Oct 2006 at 17:56

Right I think the conditional formatting should be easier.

I have a row of columns witht he date listed. If the date is in 2 days time how would I set to conditional string to colout the box yellow.
If the date in tomorrow, how do i set it blue?
And if the date is today, how do i set it green?

Can anyone tell me the string info the type in the box please.

Thanks!

Tomsk · 11 Oct 2006 at 18:35

Condition 1:
"Cell Value Is" - "equal to" - "=TODAY()"
Format -> Patterns -> colour choice

Condition 2:
"Cell Value Is" - "equal to" - "=TODAY()+1"
Format -> Patterns -> colour choice

Condition 3:
"Cell Value Is" - "equal to" - "=TODAY()+2"
Format -> Patterns -> colour choice

don't type the quotes

Ace Modder · 12 Oct 2006 at 17:31

Right got that sorted
Thanks

Just one problem. I didnt think of weekends.. any idea how to get the program to not count sat/sun......think it maybe something to do with workdays....but cannot work it out.