Conditional Formatting Help Needed!

Lauren7

New Member
Joined
Jun 3, 2015
Messages
7
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:
PackageToExpected Delivery DateActual Delivery Date
1a03-Jun-201503-Jun-2015
2a04-Jun-2015
3a02-Jun-2015
4a02-Jun-201502-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!!!!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,102
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jun 3, 2015
Messages
7
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,102
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To which range did you apply the formatting and what was the exact formula you used (copy and paste it here please)?
 

Lauren7

New Member
Joined
Jun 3, 2015
Messages
7

ADVERTISEMENT

=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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,102
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If the range to which you applied it starts in row 1, you should replace 2 with 1 in the formula.
 

Lauren7

New Member
Joined
Jun 3, 2015
Messages
7

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,102
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
My question is what range address appears in the Applies To part in the conditional formatting dialog?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,102
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That should work. Can you post the file somewhere (eg Dropbox or OneDrive) for me to have a look at?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,858
Messages
5,598,487
Members
414,243
Latest member
Shockpulsar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top