conditional formatting again :s

[SMUDGE410]

hi guys,
i have created a worksheet so i can track my workload for the service department where i work.

i have the following columns
SR/IR NUMBER
SERIAL NUMBER
CUSTOMER
TRACKING NUMBER
DELIVERED
DATE BOOKED IN
DAYS TO REC
DECON COMPLETED
ORACLE UPDATED
RTC
TRACKING NUMBER

what i want excel to do is the following - when i enter something into column A (sr/ir number) i want the whole row to turn red
then when i enter the date it arrives in colomn E (delivered) i want the row to turn orange and then when i enter a "Y" in to column I (oracle updated) it will then turn green

i have done this before but the spreedsheet has been deleted and i have no idea what to look for.

thank you in advance for your help :-)
lee

[Fotis1991]

Hi

Highlight your range.

In Conditional format rules, put these formulas.

1 rule) =$i1="Y">>>> GREEN

2 rule)=$E1<>"">>>>ORANGE

3 rule)=$A1<>"">>RED

[Pete_UK]

Presumably columns E and I are empty when the record is first created, and then E gets filled in subsequently, as does I. In that case you need to put the CF formulae in backwards, as there is a hierarchy built into the conditional formatting. Highlight all the data (and beyond) from A2 onwards, pull up the Conditional Formatting dialogue box and you should select "Formula Is" rather than "Cell Value Is" in the first box, and then enter this formula:

=$I2="Y"

Click on the Format button, choose the Patterns tab (for background colour), and then click on green. Click OK once, and then you want another condition.

For this you also want Formula Is, and use this formula:

=$E2<>""

Click Format button again, and choose Orange from the colour chart, then OK, and set up another condition.

For the 3rd condition you need this formula:

=$A2<>""

and then choose red. Finally, click OK twice to exit the CF dialogue box. Although you entered the formulae referencing row 2, Excel will automatically adjust those references to suit other rows.

Hope this helps.

Pete

EDIT: Fotis is just too quick for me !! <bg>