Conditional Formatting Help

[MollyB]

Hi,

I have a simple spreadsheet with the following columns:

Cust Name/Cust No/Planned Order Date/Actual Order Date

I need the customer record to be formatted to be red if no date exists in either date column, amber if there's a date in planned order date (but not actual order date) and green if there's a date in actual order date, even if there's also a date in planned order date.

Is this possible and if so can someone suggest a way of doing it?

Thanks

 

[c_m_s_jr]

what version of Excel are you on?

 

[MollyB]

Its Excel 2003

 

[id107]

highlight columns A:D and Format>Conditional Formatting
Change to "Formula Is" then enter these

=IF(AND($A1<>"",$C1="",$D1=""),TRUE)
Set to Red
=IF(AND($C1<>"",$D1=""),TRUE)
Set to Amber
=IF(AND($C1<>"",$D1<>""),TRUE)
Set to Green

That should do what you need.

 

[ChrisChuffy]

Well, this won't highlight the whole row, but it will give you a status box. I assume that Planned date is in column C and Actual date is in column D. Just copy this formula down in column E:

=IF(NOT(ISBLANK(D2)),3,IF(NOT(ISBLANK(C2)),2,1))

Obviously I started in row 2, you might need to update the row numbers for whatever row your data starts on. I also made the assumption that values were either dates or blanks.

Then use your normal conditional formatting to make cells with 3 green, 2 amber, and 1 red.

It might not be super elegant but it's quick and allows you to scan the list for amber and red. It has the side benefit of making your list sortable by status as well, with the most urgent items at the top.

 

[MollyB]

Many thanks for the responses...think I'll go with the second option, as it would be useful to be able to sort the data.

Thanks again!