Conditional Formatting Conundrum

[Suedonym]

Hi all.

I'm having something of a conundrum with conditional formatting
Basically i've got a spreadsheet with training qualifications and thei
expiry dates listed on it.
I've got it working so that the cells with the dates are highlighted re
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 blan
highlight because I found the cell turned red when no value was presen
in it. Note: It's only purpose is to keep the cell white until Conditio
2 is met.
This all works perfectly fine.

My problem is that my superior has asked me to add in a second conditio
between these two that highlights the expiry date cell orange when th
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'
causing a problem, as that is just set to + rather than - from F5. So
think it's just the fact that I've got the Cell Value Is Betwee
condition written wrong.

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

Cheers,
Suedony

 

[GS]

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

 

[Suedonym]

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

Suedony

 

[GS]

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