How do i make a range of cells change colour
 

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

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

Cheers


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398543

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

--
HTH

Bob Phillips

"Spiller" wrote in message
...
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

hi,
high light a single row. assuming date is in column C.
formatconditional 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" wrote:

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

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

"FSt1" wrote:

hi,
high light a single row. assuming date is in column C.
formatconditional 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" wrote:

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

Do them the other way around with these formulas

=$C2<=TODAY() - red

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

--
HTH

Bob Phillips

"Spiller" wrote in message
...
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

"FSt1" wrote:

hi,
high light a single row. assuming date is in column C.
formatconditional 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" wrote:

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

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

"Bob Phillips" wrote:

Do them the other way around with these formulas

=$C2<=TODAY() - red

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

--
HTH

Bob Phillips

"Spiller" wrote in message
...
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

"FSt1" wrote:

hi,
high light a single row. assuming date is in column C.
formatconditional 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" wrote:

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