Thread: Changing cell colour within IF statements

Hi,
I have a spreadsheet with a master worksheet containing columns that list staff names and the training completed and forklift licenses obtained and when those licenses expire.
Another worksheet refers to that master sheet and using nested IF statements populates the cells with the names of staff who have obtained their forklift licenses and when the licenses expire.
Is there any way that I can expand the IF statements (or add something else) to change the cell colour depending on how close each cell name is to the license expiry date? e.g. change the cell colour to orange if the expiry date is within 6 weeks from today's date, red if within 3 weeks, and so on.

you can use conditional format >>> if you could post a small example of your file would help

Thanks for your reply. I've attached a small example to look at.

Hi, you can use conditional formatting.
With expiry date in column B, select the cells you want formatted and apply these conditions
=$B2-today()<=21
=$B2-today()<=42
For each condition, you choose what color should be used when condition is true.

Sorry it took so long to get back to you >> I was called away
click B10 go conditional format / formula is

=B10<=TODAY()+21

format your color add

=B10<=TODAY()+42

format your color

Fantastic - works a treat thank you. Now tell me is there a way to copy this conditional rule across multiple rows without doing each cell individually? I would have say 4 conditional formats per cell (4 different colours for different upcoming lead times to license expiration date) so across a thousand rows it would save me heaps of effort. Thanks again for bothering to help out.

This may work better

=AND($B10<TODAY(),$B10>TODAY()-21)

and format colour

=AND($B10<TODAY(),$B10>TODAY()-42)

and format colour
now click B10 Copy then highlight were you want cells formated -- go Paste special / click FORMATS then OK

Thanks Guys - terrific help!