Conditional Formatting with data validation list

fatekeeper

New Member
Joined
Nov 12, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I am trying to create an excel file that will allow order information to be entered and then to allow for the status of each line to be tracked and also to have color coding to flag orders depending on the ship date.

I created a worksheet that uses a drop down box (pulls from a list on a different worksheet in the same workbook) so the user can select different statuses, and I applied conditional formatting to change the color of each line slightly so the users know at a glance which orders are in which status. My problem comes when trying to apply conditional formatting to cells in a column that displays the estimated ship date. I want the cell with the ship date and to turn green fill for orders shipping within the next week (7 days) to help flag them for follow-up and red fill if the date has already passed, but not to change if the status column indicates it has already shipped.
I have the conditional formatting rule for Shipped status as the first one have it marked to stop if that rule is triggered, which is working as expected. My issue is that now when any other status is selected, if there is not yet a date in the ship date field, it remains red. I would like it to stay the same color as the status would indicate UNLESS it's a date in the next 7 days (fill green) or a past date but not yet shipped (fill red) - is there a way to do this?

Enersys Order Tracker.xlsx
ABCDEFGH
1Date SubmittedCustomerPO#MATERIALQuote#ESDNotesStatus
211/1/20211234SAMPLE1QN2021-0812/30/2021Shipped
312/20/20215678SAMPLE2QN2022-0212/31/2021OA Received
41/3/20222468SAMPLE3QN2022-01Submitted
5
Open Orders
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F3:F5Expression=$H3="Shipped"textYES
F3:F5Expression=AND(F3>TODAY(),F3<=(TODAY()+7))textNO
F3:F5Expression=$F3=(TODAY()-$F$2)>=1textNO
F3:F5Expression=$H3="OA Received"textNO
F3:F5Expression=$H3="Submitted"textNO
A2:G2Expression=$H2="Shipped"textYES
F2Expression=AND(F2>TODAY(),F2<=(TODAY()+7))textNO
H3:H2000Cell Value=Lists!$A$4textNO
H3:H2000Cell Value=Lists!$A$3textNO
H3:H2000Cell Value=Lists!$A$2textNO
A6:G2050,A5:E5,G5Expression=$H5="Shipped"textNO
A6:G2050,A5:E5,G5Expression=$H5="OA Received"textNO
A6:G2050,A5:E5,G5Expression=$H5="Submitted"textNO
A3:E4,G3:G4Expression=$H3="Shipped"textNO
A3:E4,G3:G4Expression=$H3="OA Received"textNO
A3:E4,G3:G4Expression=$H3="Submitted"textNO
F2Expression=$F2=(TODAY()-$F$2)>=1textNO
A2:G2Expression=$H2="OA Received"textNO
A2:G2Expression=$H2="Submitted"textNO
H2Cell Value=Lists!$A$4textNO
H2Cell Value=Lists!$A$3textNO
H2Cell Value=Lists!$A$2textNO
Cells with Data Validation
CellAllowCriteria
H2:H4List=Lists!$A:$A
H5List=Lists!$A:$A
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Hi Fatekeeper,

Try
Excel Formula:
=AND($F3<>"",($F3=(TODAY()-$F$2)>=1))
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
You're welcome. Glad to help.
 

fatekeeper

New Member
Joined
Nov 12, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi again! Adding this as a comment instead as a new thread since it is related and I think the previous history might be useful. Now that the user is inputting data, some of the rules aren't working.
Basically, if the status from the drop down list is "Shipped", the cells behave as desired, and if the estimated ship date falls within 7 days from today the cells also behave as desired (fill green in date column).
But for any other status selected and regardless of the date entered in the date column (except for those within 7 days in the future from today), all cells in the date column that have a date entered are filling red, which should only happen for dates that occur prior to today. The rule appears to be the same as previously advised to try, and it worked when I tried it originally but now that the user is manipulating the data I cannot for the life of me figure out why it's not working. I've tried a bunch of different changes but none give the desired result. I've attached a screenshot for the rules in place when cell F3 is selected - hopefully a more experienced set of eyes can see what I'm obviously overlooking and explain what I'm doing wrong? Thank you.
 

Attachments

  • Screenshot Manage Rules.jpg
    Screenshot Manage Rules.jpg
    54.9 KB · Views: 6

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Please supply more data using XL2BB.
Your red CF is always based on cell $F$2 but without seeing more sample data I'm not sure if it should be relative.
 

fatekeeper

New Member
Joined
Nov 12, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

SAMPLE_TRACKER.xlsx
ABCDEFGH
1Date SubmittedCustomerPO#MATERIAL OR PROJECTQuote#ESDNotesStatus
210/1/20211295542100574012BATTERY & RACK4/1/2022OA Received
310/16/2021100156289206BATTERY & RACK4/1/2022OA Received
411/4/20211001139742142BATTERY2/11/2022OA Received
511/19/20211000999782664BATTERY6/3/2022OA Received
611/26/20211262374500311290BATTERY12/10/2021Shipped
712/2/2021157666105144TP23/18/2022OA Received
811/23/2021157666105237TP5D12/13/2022OA Received
911/23/2021157666105219TP5B12/13/2022OA Received
1011/12/2021157666104962TP13/11/2022OA Received
1111/23/2021157666105153TP5A12/13/2022OA Received
1211/23/2021157666105228TP5C12/13/2022OA Received
1311/19/2021157666105091TP312/30/2022OA Received
1412/2/20211001119808681BATTERY6/17/2022OA Received
1512/1/20211001119803559BATTERY3/10/2022OA Received
1611/19/20211123294500728994BATTERY2/25/2022OA Received
1712/6/20211001009799030RACK1/14/2022OA Received
1811/26/20211123294500730746BATTERY1/31/2022OA Received
1912/10/20211001009799065RACK1/26/2022OA Received
2012/11/2021100156290689BATTERY & charger6/24/2022OA Received
2112/17/20211001009834933RACK2/9/2022OA Received
2212/22/20211295542100591760RACK3/4/2022OA Received
2312/22/20211295482100589581RACK2/18/2022OA Received
2412/15/20211001009823941BATTERY3/25/2022OA Received
2512/23/20211001119844665BATTERY1/27/2022OA Received
2612/20/202118498544279-704091205BATTERY, RACK, & CHARGER4/26/2022OA Received
2712/23/20211000999853564RACK2/16/2022OA Received
2812/16/20211001009828307RACK2/9/2022OA Received
STATUS TRACKER
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H3:H28Expression=$H3="Shipped"textNO
H3:H28Expression=$H3="OA Received"textNO
H3:H28Expression=$H3="Submitted"textNO
H2Expression=$H2="Shipped"textNO
H2Expression=$H2="OA Received"textNO
H2Expression=$H2="Submitted"textNO
F2:F2021Expression=AND(F2>TODAY(),F2<=(TODAY()+7))textNO
F2:F2021Expression=AND(F2>TODAY(),F2<=(TODAY()-7))textNO
F2:F2021Expression=$H2="Shipped"textYES
A4:C4,G4,B14:B15,B25Expression=$H4="Shipped"textNO
A4:C4,G4,B14:B15,B25Expression=$H4="OA Received"textNO
A4:C4,G4,B14:B15,B25Expression=$H4="Submitted"textNO
G2:G3,D4:E4,A14:A15,C14:G14,A26:G2049,A25,A2:E3,A5:G13,C15,A16:C16,D15:G16,A17:G24,C25:G25Expression=$H2="Shipped"textNO
G2:G3,D4:E4,A14:A15,C14:G14,A26:G2049,A25,A2:E3,A5:G13,C15,A16:C16,D15:G16,A17:G24,C25:G25Expression=$H2="OA Received"textNO
G2:G3,D4:E4,A14:A15,C14:G14,A26:G2049,A25,A2:E3,A5:G13,C15,A16:C16,D15:G16,A17:G24,C25:G25Expression=$H2="Submitted"textNO
F2:F2021Expression=$H2="OA Received"textNO
F2:F2021Expression=$H2="Submitted"textNO
Cells with Data Validation
CellAllowCriteria
H2:H28List=Lists!$A$1:$A$4
 

fatekeeper

New Member
Joined
Nov 12, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I'm trying to have the color of the cell in column F change dependent mostly on the date but also on the status:
If status in column H for the row is "Shipped", then the color of the cell in column F of that row should be blue like the rest of the row (as seen on Line 6).
If the date in column F is within seven days of today's date, I want it to fill green (as seen in row 19).
But, if the date in column F is a past date, then I'd like that cell with that date to have a red fill - so row 17 should be red filled because 1/14/22 is a past date prior to today's date, with today being whatever day the user opens the file, but it's not.
The other dates in column F should remain whatever color the conditional formatting for the associated status. This seems to be working.

I feel like I've almost got it, but not quite! I really appreciate you taking the time to review and offer your assistance.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Try,
Excel Formula:
=AND(F2<>"",F2<=(TODAY()-7))
if you want it to highlight when at least 7 days earlier (so 1/14/2022 will highlight on 21 Jan 2022)
or
Excel Formula:
=AND(F2<>"",F2<TODAY())
if it should highlight when earlier than today.
If it must not highlight for "Shipped" status then
Excel Formula:
=AND(F2<>"",F2<>"Shipped",F2<TODAY())
 
Solution

fatekeeper

New Member
Joined
Nov 12, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Ahhh... I didn't notice that the "1" had been changed to a "7". Thanks for pointing that out in your reply. I used solution =AND(F2<>"",F2<>"Shipped",F2<TODAY()) and made sure the CF for "Shipped" was the first rule with it checked to stop checking other rules if true and it seems to be working perfectly now. Thanks again!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,313
Messages
5,836,594
Members
430,441
Latest member
SurendraTantia

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