Conditional Formatting Conundrum
 

Hi all.

I'm having something of a conundrum with conditional formatting. Basically i've got a spreadsheet with training qualifications and their expiry dates listed on it.
I've got it working so that the cells with the dates are highlighted red when todays date is within 30 days of the expiry date:

Formula Is =TODAY()=F5-30

I have also set up Formula Is =TODAY()=F5+31 which triggers a blank highlight because I found the cell turned red when no value was present in it. Note: It's only purpose is to keep the cell white until Condition 2 is met.
This all works perfectly fine.

My problem is that my superior has asked me to add in a second condition between these two that highlights the expiry date cell orange when the TODAY() value is in the 60 to 31 days period before the expiry date. I've tried using the Cell Value Is Between condition but to no avail. Formula I tried was:

Cell Value Is Between =TODAY()=F5-60 AND =TODAY()=F5-31

As far as I can guess, it's not Condition 1 (the blank formula) that's causing a problem, as that is just set to + rather than - from F5. So I think it's just the fact that I've got the Cell Value Is Between condition written wrong.

Any clues or even answers to this would be greatly appreciated.

Cheers,
Suedonym

Conditional Formatting Conundrum
 

Suedonym laid this down on his screen :
Hi all.

I'm having something of a conundrum with conditional formatting.
Basically i've got a spreadsheet with training qualifications and their
expiry dates listed on it.
I've got it working so that the cells with the dates are highlighted red
when todays date is within 30 days of the expiry date:

Formula Is =TODAY()=F5-30

I have also set up Formula Is =TODAY()=F5+31 which triggers a blank
highlight because I found the cell turned red when no value was present
in it. Note: It's only purpose is to keep the cell white until Condition
2 is met.
This all works perfectly fine.

My problem is that my superior has asked me to add in a second condition
between these two that highlights the expiry date cell orange when the
TODAY() value is in the 60 to 31 days period before the expiry date.
I've tried using the Cell Value Is Between condition but to no avail.
Formula I tried was:

Cell Value Is Between =TODAY()=F5-60 AND =TODAY()=F5-31

As far as I can guess, it's not Condition 1 (the blank formula) that's
causing a problem, as that is just set to + rather than - from F5. So I
think it's just the fact that I've got the Cell Value Is Between
condition written wrong.

Any clues or even answers to this would be greatly appreciated.

Cheers,
Suedonym

Use the AND() function so you only have 2 format conditions. Place the
least likely condition ahead of the most likely...

Condition1:
Formula Is: =AND(F5<"",(F5-31)TODAY(),(F5-60)<TODAY())

Condition1:
Formula Is: =AND(F5<"",(F5-31)<TODAY())

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks for the reply Garry, and sorry I didn't say thank you sooner as i've been on holiday. That worked nicely thank you.

Suedonym

Conditional Formatting Conundrum
 

Suedonym laid this down on his screen :
Thanks for the reply Garry, and sorry I didn't say thank you sooner as
i've been on holiday. That worked nicely thank you.

Suedonym

You're welcome! ..feedback is always appreciated!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion