The CF formula you want in row 2 is =$E2-30<TODAY()

Either select the whole range (with the active cell being in row 2) and then
insert the above as your CF formula and it will update the formula for
susequent rows.
Or Put the above condition in row 2 and use Format Painter to copy down
Or Put the above condition in row 2, copy that cell, and use Edit/ Paste
special/ Format to paste the format down your range.
--
David Biddulph

"Gareth" wrote in message
...
Hi there Pyrite, thanks for your messages, trying to get my head around
it.
I am using Excel 2007.

I am trying to put in the following formula

=$E$2:$E$34-30<TODAY()

I am using colum E to put all the dates in.

Obvioulsy what i want is excel to highlight the cell which is within 30
days
(or whatever number of days) of todays date.

the above formula doesnt seem to do anything.

any clues?

Alternativly, is there a way to enter the formula in yourself and email me
a
copy so i can see what you have done

Thanks




"Pyrite" wrote:

To elaborate a little more, in cell A1 you need to make sure that the
formula
in the conditional formatting reads A1 and then in A2 it must read A2
etc.

If you dont have dates in the range already you could format the first
cell
to conditonal format and then simply copy down the row. If you intend to
do
this change the formula slightly by removing the $ symbols, like this
=A1-30<TODAY()

When you copy this down it should change the cell reference
automatically.
To check this just select a few individual cells at random and open the
conditional formatting dialogue box via the Format menu and make sure
they
refer to themselves. You can also test by putting dates in that should
react
and ones that shouldnt.

"Gareth" wrote:

Hi Pyrite

thanks for your quick response.

I cant seem to find conditioning format. I have added conditional
formatting
to the ribbon, however when selected, this only gives options on things
like
highlight cells
top/bottom rules,, data bars etc. nor is there anywhere that says
Forumla Is.

any clues?

G

"Pyrite" wrote:

To answer the date question you could use a conditional format on the
cell
containing the date (A1 in this example). Set the condition to
Formula Is and
then enter =$A$1-30<TODAY() and then set the formatting to whatever
you
require i.e. Bold and Red.

This will then highlight the date when it comes within 30 days of
today's
date.

Hope that helps.

"Gareth" wrote:

Hi all

I have 100+ clients on excel, all have certain certificates with
different
expiry dates.

What I would like is either of the two.

1: The to highlight in red say if the expiry date is within 30-60
days of
todays date.
2: Have a seperate worksheet available whereby it will update
automatically
with the name and certification that will expiry and of course
when.

Obviously the reason for this is to ensure I remain compliant and
chase
those candidates with soon to expire qualifications.

Thanks in advance :)
G