Conditional Formatting Help Needed!

[Lauren7]

Hi,

I'm hoping someone can help!! I'm trying to modernise the way some things are done in my office.. one of those being the tracking of shipments we send out using an excel spreadsheet.

So far this is working great, however I'm struggling with conditional formatting.

I have a column (J) which is for "expected delivery date". In this column I write the date we expect the shipment to arrive.
I have a column (K) which is for "actual delivery date". In this column I enter the date when we receive confirmation of delivery.

I would like to set up conditional formatting to do the following:

Highlight in red when a date in column J is either today's date, or a date in the past but ONLY if column K is blank. Similar to this:

Package	To	Expected Delivery Date	Actual Delivery Date
1	a	03-Jun-2015	03-Jun-2015
2	a	04-Jun-2015
3	a	02-Jun-2015
4	a	02-Jun-2015	02-Jun-2015

<tbody>
</tbody>

Is this possible? Ideally I'd like to format the entire row for the package which is late. If this is not possible then simply highlight the cell where the expected delivery date is would be sufficient!

Thank you for your help!!!!

 

[RoryA]

Hi, and welcome to the forum.

You can use a formula in the CF dialog to determine which cells to format.

Assuming you are applying it to a range starting in row 2, you could use:
=AND($K2="",$J2<=TODAY())
and then choose the formatting you want. Note that there are $ before the J and K but not before the row number.

 

[Lauren7]

Hi, and welcome to the forum.

You can use a formula in the CF dialog to determine which cells to format.

Assuming you are applying it to a range starting in row 2, you could use:
=AND($K2="",$J2<=TODAY())
and then choose the formatting you want. Note that there are $ before the J and K but not before the row number.

Thanks for your help! I've applied this formula however it's not working... It's formatted a row which shouldn't be formatted as the date it's expected to be delivered is tomorrow.... I have other rows in the workbook I'm trying to work on which are also expected to be delivered tomorrow, and it's not formatted these.

I'm VERY confused

 

[RoryA]

To which range did you apply the formatting and what was the exact formula you used (copy and paste it here please)?

 

[Lauren7]

=AND($K2="",$J2<=TODAY())

That's the formula I used for the entire workbook.

It's been a few years since I used excel formulas I'm now a novice again!!

 

[RoryA]

If the range to which you applied it starts in row 1, you should replace 2 with 1 in the formula.

 

[Lauren7]

row one is the titles, the data starts in row 2. Very confused!

 

[RoryA]

My question is what range address appears in the Applies To part in the conditional formatting dialog?

 

[Lauren7]

=$2:$1048576

 

[RoryA]

That should work. Can you post the file somewhere (eg Dropbox or OneDrive) for me to have a look at?