Conditional Formatting Combined with Dropdowns and Dates

melissalk

Hello!

I have a user tracking the progression of course development and is asking if there is a way to do some formatting on his sheet.

• The columns shown are set up as dropdowns (there are 8 total columns on this sheet).
  
• In the yellow row, in the cells where I've drawn a box, he is going to place due dates.
  
• Is it possible to change cell color based on the dropdown selection AND that due date?

Example: If Not Started is selected in the Initial sweep column AND it's a specific date (which will be in the yellow cell of that column), turn the Not Started cell pink.

My initial solution: Having the column properties be Date columns (instead of dropdowns) and then using conditional formatting to change the cell color based on the text entry does work, but the user entering the text would need to be consistent with their entries. I think it's a possible solution, but I feel I might be missing some other options (formulas, Helper column(s), etc.).

Am I on track or has anyone done anything similar?
Many thanks!
-Melissa

Scarlet Johns

You're on the right track! A solid solution is to keep the dropdowns for status and use conditional formatting with a custom formula that references both the dropdown value and the due date in the yellow cell. For example, use a formula like =AND(A2="Not Started", A$1=TODAY()) to turn the status cell pink when it matches the condition. No need to ditch dropdowns or rely on perfect text entry—this keeps it clean, dynamic, and easy to update, even if it's a Scarlet direct install sheet.

melissalk

Hi @Scarlet Johns! Thank you for the response! I gave it a try and got unparseable. Is the formula placed in each cell, adjusting the letter/number based off the cell location? Also, I'm not sure what Scarlet direct install sheet is..?

Is this possible - Lets say the date in the yellow cell is 5/1/2025 and the dropdown for a task cell in the same column is Not started. Can a formula keep that cell white until it is 5/1/2025 and then on that date, turn the cell pink? (that may be what you answered, but I could be misinterpreting it).