i want to automatically colour fill a cell with a specific colour depending on the date contained in another cell, any idea how to do it. i need to keep the text in the cells also.

for example.

IF the date in cell A10 is before the current date. highlight cell A1 in red

Many thanks

[Edited on 2/2/07 by liam.mccaffrey]

You need to use Conditional formatting mate. You can find it under the 'Format' drop down menu.

Yep conditional formatting.

Just use a if statement and apply it to the cells in question. Should do it...

i know about conditional formatting, theres already a lot of it on my sheet.

what i think i need to do now is conditionally format with a couple of IF and OR's

I think ive sussed it now when i looked at it first it seemed more complicated than it was

you need to select Formula instead of Cell

e.g. if you want to colour Cell A2 depending on what is in A1, you would do something like:

1. position the cursor on A2
2. Format->Conditional Formatting
3. Formula is =A1="RED"
4. Click Format
5. Click Patterns, select the red colour and click OK
6. Click OK again

Then, if A1 contains RED, cell A2 will be coloured red

You can add up to 3 conditions for a given cell

have fun!

cool done it now thanks chaps
used
=OR($I$82<TODAY(),$L$82<TODAY())
for condition 1
=OR($I$82<TODAY()+550,$L$82<TODAY()+550)
for condition 2

this will highlight the cell red if its over due and pink if it goes out of compliance within 18 months of todays date