Help requested for conditional formatting

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

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.
 
In microsoft.public.excel.misc on Sun, 19 Aug 2007, David Biddulph
CF/ Formula Is/ =TODAY()-A1>5
or
CF/ Cell Value Is/ less than/ =TODAY()-5
Click to expand...

I'll try those & get back to you.

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


I'll try those & get back to you.
Click to expand...

OK - that first one works!

I didn't even know about the 'Formula Is' option, before! :)
 
In microsoft.public.excel.misc on Mon, 20 Aug 2007, Paul Hyett
In microsoft.public.excel.misc on Sun, 19 Aug 2007, Paul Hyett


OK - that first one works!

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

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?
 
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.
 
In microsoft.public.excel.misc on Mon, 20 Aug 2007, David Biddulph
Yes. And if you are wanting to combine two functions, the AND function is
likely to be required too.
Click to expand...
Details of all functions (except DATEDIF) are in Excel help, usually with
examples and with links to related functions through the "See also" link.
Click to expand...

Believe me, I do *try* using Excel help, but it so rarely gives examples
in the form I'm looking for.
 
Paul Hyett said:
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?
Click to expand...

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
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
Click to expand...

Looks impressive - I just wish I could understand it. :)
 
Paul Hyett said:
In microsoft.public.excel.misc on Tue, 21 Aug 2007, Dallman Ross


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

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=
 
Back
Top