I would like to set up a conditional format whereby if today's date
minus [date in referenced cell] was greater than a certain number, then
the fill colour of the cell would be changed.

I've tried experimenting with setting this up, but just don't seem to be
able to hit the right syntax.

Any assistance would be much appreciated.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

CF/ Formula Is/ =TODAY()-A15
or
CF/ Cell Value Is/ less than/ =TODAY()-5
--
David Biddulph

"Paul Hyett" wrote in message
...
I would like to set up a conditional format whereby if today's date minus
[date in referenced cell] was greater than a certain number, then the fill
colour of the cell would be changed.

I've tried experimenting with setting this up, but just don't seem to be
able to hit the right syntax.

Any assistance would be much appreciated.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Sun, 19 Aug 2007, David Biddulph
wrote :

CF/ Formula Is/ =TODAY()-A15
or
CF/ Cell Value Is/ less than/ =TODAY()-5

I'll try those & get back to you.

Thanks.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Sun, 19 Aug 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Sun, 19 Aug 2007, David Biddulph
wrote :

CF/ Formula Is/ =TODAY()-A15
or
CF/ Cell Value Is/ less than/ =TODAY()-5

I'll try those & get back to you.

OK - that first one works!

I didn't even know about the 'Formula Is' option, before! :)
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Mon, 20 Aug 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Sun, 19 Aug 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Sun, 19 Aug 2007, David Biddulph
wrote :

CF/ Formula Is/ =TODAY()-A15
or
CF/ Cell Value Is/ less than/ =TODAY()-5

I'll try those & get back to you.

OK - that first one works!

I didn't even know about the 'Formula Is' option, before! :)

BTW, a follow-up : I'd like the cell fill colour changing only if it
wasn't empty. Presumably the 'ISBLANK' function would be involved
somehow?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Yes. And if you are wanting to combine two functions, the AND function is
likely to be required too.
Details of all functions (except DATEDIF) are in Excel help, usually with
examples and with links to related functions through the "See also" link.
--
David Biddulph

"Paul Hyett" wrote in message
...
In microsoft.public.excel.misc on Mon, 20 Aug 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Sun, 19 Aug 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Sun, 19 Aug 2007, David Biddulph
wrote :

CF/ Formula Is/ =TODAY()-A15
or
CF/ Cell Value Is/ less than/ =TODAY()-5

I'll try those & get back to you.

OK - that first one works!

I didn't even know about the 'Formula Is' option, before! :)

BTW, a follow-up : I'd like the cell fill colour changing only if it
wasn't empty. Presumably the 'ISBLANK' function would be involved somehow?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Mon, 20 Aug 2007, David Biddulph
wrote :

Yes. And if you are wanting to combine two functions, the AND function is
likely to be required too.

Details of all functions (except DATEDIF) are in Excel help, usually with
examples and with links to related functions through the "See also" link.

Believe me, I do *try* using Excel help, but it so rarely gives examples
in the form I'm looking for.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In , Paul Hyett
spake thusly:

BTW, a follow-up : I'd like the cell fill colour changing only
if it wasn't empty. Presumably the 'ISBLANK' function would be
involved somehow?

Not a direct answer to your question, which I believe has been
answered at any rate -- but I coincidentally just finished working
out a reusable module to handle some Conditional Formatting functions.
It's working out to be rather nifty for me, so I've made a web page
about it and am happy to give away the code.

http://heliotropos.com/xl/code/cfmagic.html

=dman=

In microsoft.public.excel.misc on Tue, 21 Aug 2007, Dallman Ross
wrote :
In , Paul Hyett
spake thusly:

BTW, a follow-up : I'd like the cell fill colour changing only
if it wasn't empty. Presumably the 'ISBLANK' function would be
involved somehow?

Not a direct answer to your question, which I believe has been
answered at any rate -- but I coincidentally just finished working
out a reusable module to handle some Conditional Formatting functions.
It's working out to be rather nifty for me, so I've made a web page
about it and am happy to give away the code.

http://heliotropos.com/xl/code/cfmagic.html

Looks impressive - I just wish I could understand it. :)
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In , Paul Hyett
spake thusly:

In microsoft.public.excel.misc on Tue, 21 Aug 2007, Dallman Ross
wrote :

I coincidentally just finished working out a reusable module to
handle some Conditional Formatting functions. It's working out
to be rather nifty for me, so I've made a web page about it and
am happy to give away the code.

http://heliotropos.com/xl/code/cfmagic.html

Looks impressive - I just wish I could understand it. :)

Well, if I can explain anything, let me know.

Basically, it's for if you're coding page formats in VBA directly
and not via the CF dialog pulldown from the Excel Format menu.
The CF code is long and messy, and if you have a sheet that uses
a lot of it you will fill up your modules with gobbledygook
pretty fast. I didn't like that, so went about creating a
reusable submodule you call from your main code. You just feed
it the CF formulas, the (bottom) border color, and the fill
colors you'll want.

The formulas I show in the sample, you could work through by
simply typing (or copying and pasting) them into the CF dialog
region directly to see what they do (on a sample worksheet,
of course).

=dman=