Conditional Formating a whole spreadsheet

[TORBABY]

I am really stuck at the moment when i am trying to conditional format a spreadsheet in Excel 2003.
I am able to set each cell up with 3 formats to change colour when the date in it goes above a certain amount of days (e.g 365, 90 or 30 days)
However the size of my spreadsheet makes this impossible to do with one cell at a time as this will take days. Is there any way that i could set the format so it would do a row at a time or even the whole spreadsheet?

I can send a copy of part of the spreadsheet that i am using if this will help.

Thanks!

 

[VoG]

There are two ways to do that:

1. Select all cells then apply the CF using a formula referring to the top left cell in your selection, e.g.

=A1>10

2. Apply the CF format to a cell then use the Format Painter (paintbrush icon on the toolbar) to copy to other cells.

 

[jasonb75]

Select the range you want the format applied to before you create the condition(s).

Remember to use relative / absolute references as appropriate.

 

[TORBABY]

the format painter seems to work however i have noticed that it will take one of the formats and copy it across instead of if i paste it onto a date which is 28/03/2011 ( this should higligh green because it is less than 30 days ago) this just turns red. Is there anything i can do to resoleve this?

 

[VoG]

I think that the order would be important

1 test for <=30 days
2 test for <=90 days
3 test for <=365 days

or similar.

 

[TORBABY]

now that i have changed the order the formatting doesnt work it leaves my cell white.
The conditions that i have are below ( an example)
1. =IF(TODAY() - $C$3 >365, TRUE, FALSE) - this is formatted to change red
2. =IF(TODAY() - $C$3 >90 , TRUE, FALSE) - this is formatted to change to yellow
3. =IF(TODAY() - $C$3 >30, TRUE, FALSE) - this is formatted to change to green

 

[VoG]

For CF you only need

=TODAY() - $C$3 >365

but this is checking against cell C3 in every other cell which surely isn't correct. Should it be

=TODAY() - $C3 >365

for example?

 

[TORBABY]

that has worked slightly better. Although there is a date that should be showing as red (01/10/2009) as it more than 365 days ago but it is showing as green instead

 

[TORBABY]

The formula is still not working. It turns a whole row the same colour no matter if one of the dates is more than 365. it seems to be going off the first column. Is there something wrong with the formula that i am using??

 

[VoG]

If you are trying to check each cell separately then the CF formula should be like this

=TODAY()-C3>365