How do i make a range of cells change colour

G

Guest

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
 
J

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
 
B

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
 
G

Guest

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
 
G

Guest

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
 
B

Bob Phillips

Do them the other way around with these formulas

=$C2<=TODAY() - red

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

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top