Conditional Formatting: If a cell is blank after due date, highlight said cell

**Deaco** · 08-06-2019, 07:03 AM

Hi,

I've attached a spreadsheet example where I am looking to implement change.

What I want the spreadsheet to show is if there isnt a "p" in a cell before the week commencing date, I want this cell to be highlighted (red for instance).

This is so i can track if a person has paid and can identify how far behind they are.

So far I have tried this formula within conditional formatting, (E2="","",E2<=TODAY()) and applied this to all columns, with cell ref alteration. This works to highlight blank cells, but also highlights cells with a "p" which I cannot figure out how to stop.

Any help or suggestions would be much appreciated, thank you.

EDIT: I forgot to mention any future dates that havent happened yet, these need to remain white/blank until that date arrives and cells can then change colour if blank

**SjMaxwell** · 08-06-2019, 07:41 AM

I used =AI$2>Today() to format the blanks after today and an excel rule that formats only cells that contain to color the blanks. Clicking the Stop If True checkbox keeps the red out of the future dates. See attached images
Attachment 635948 Attachment 635949 Attachment 635950

**Deaco** · 08-06-2019, 07:44 AM

Hi thanks for the response but the attachments are coming back as invalid when I go to follow them.

**SjMaxwell** · 08-06-2019, 07:57 AM

Okay, to get the blanks red(or whatever color) click conditional formatting --> New Rule --> Format only cells that contain.... The first drop box should say Cell Value. Change that to Blanks and set your color. Then do another new rule --> Use a formula to determine which cells to format. the formula is =AI$2>Today and leave it with no format set. When you view your rules, Manage Rules, you'll want to see the blank cell coloring format on the bottom and the date formatting at the top. Check the Stop if true box next to the date formatting.

**Deaco** · 08-06-2019, 08:08 AM

Thank you for breaking that down, I never thought to split across two conditions. That is working as expected now thank you.

Conditional Formatting: If a cell is blank after due date, highlight said cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional Formatting: If a cell is blank after due date, highlight said cell

Re: Conditional Formatting: If a cell is blank after due date, highlight said cell

Re: Conditional Formatting: If a cell is blank after due date, highlight said cell

Re: Conditional Formatting: If a cell is blank after due date, highlight said cell

Re: Conditional Formatting: If a cell is blank after due date, highlight said cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] highlight cell through conditional formatting if the date matches ?

How to use Conditional Formatting to highlight a cell based from that cell's date?

Conditional Formatting to Highlight Row if cell is not blank

[SOLVED] Use Conditional Formatting to Highlight Date Due if Text in Another Cell

Conditional Formatting If cell contains data highlight red if blank do nothing

Conditional formatting: if date is before today highlight only IF adjacent cell is blank

highlight or conditional formatting with date/time value in cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions