How do i make a range of cells change colour

[Spiller]

Hi,

I am trying to compile a spreadsheet for employees on visa's. The table will
look like this:

Name Type of Visa Expiry Date
Miss X Student 01/09/2005
Mr Y Working 28/12/2005

When a visa is within 30 days of it expiring i want the cell (with the
expiry date in it) to turn orange, when it has reached the expiry date i want
the cell to turn red. All other dates before this i want to remain in black.

I have managed to make this work on just one cell (under conditional
formatting) but i need it to work for about 100 employee names.

Can anyone please help me with this query as it is driving me crazy!

Many thanks

 

[Jon Quixley]

If you have been able to get this to work with the one cell, then al
you need to do is copy the FORMAT and paste it into the other cells. T
do this, you can either use the format painter (this is an icon tha
looks like a 2 inch paintbrush). Highlight the cell that works with th
conditions and click on the format painter and then put the cursor o
the cell you want to format with the conditions.
The alternative way is to Ctrl-C copy the cell that has had th
conditionning done and Paste Special/FORMATS the new cells

Cheer

 

[Bob Phillips]

Select all the target cells (assuming C2 is the first)

Go to CF

Change Condition 1 to Formula Is

Add a formula of =C2<=TODAY()

Click format

select pattern

choose red

exit

 

[FSt1]

hi,
high light a single row. assuming date is in column C.
format>conditional formatting

condition 1
formual is =if($c2<Now()+30,1,0) format = orange
1 = true, 0 = false.

condition 2
Formula is = if($c2<=Now(),1,0) format = red

copy the row
high light the remaining range of employees.
Paste special formats only.

regards
FSt1

 

[Spiller]

Hi there,

Thanks so much for your help. I can now make the approprioate dates go
orange but the formula for them going red when they have expired does not
seem to be working.

Is there anything else i can do?

Spiller

 

[Bob Phillips]

Do them the other way around with these formulas

=$C2<=TODAY() - red

=$C2<=TODAY()+30 - amber

 

[Spiller]

You star!!!!

Thank you so much.

I wish i knew this help was here a lot earlier, i've been trying to work
that problem out for days now!

Spiller