# Conditional formatting in Excel for blank values & dates

#### David77

##### Board Regular
Hello everybody,

To make this quick, i have the following setup.

I want to create a conditional formatting formula so whenever I have a value in column M (basically whenever the corresponding cell in column M is not blank), then my corresponding cell in column G should turn yellow.

How do I do this? Does anybody know?

Furthermore,

I have the following setup on the right side in my excel sheet:

Is it possible to create a conditional formatting so once the order date has been entered into Excel in column L and we have passed that date + 4 additional days, the designated cell value in column L gets highlighted with a red color?

So for example, if cell L1 has order date 16-10-2020 and we are on the 20-10-2020 now (4 days later) and the value in cell M1 has not been removed (so it still has the long number 8000166464/8000216970), then cell L1 will be highlighted with a red color until the value from cell M1 is removed and that cell goes blank.

I hope this makes sense!

I would really appreciate some help here, I cannot explain how much this would mean to me, it would truly make my day! Thank you so very much everybody

PLEASE let me know if you want more photos or information as well! I will be happy to respond ASAP!

Kind regards,
David

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Fluff

##### MrExcel MVP, Moderator
For column G you can use
Excel Formula:
``=M2<>""``
and for L
Excel Formula:
``=AND(L2<=TODAY()-4,M2<>"")``

For future reference you would do better to post data to the board using the XL2BB add-in, rather than an image.

#### David77

##### Board Regular
For column G you can use
Excel Formula:
``=M2<>""``
and for L
Excel Formula:
``=AND(L2<=TODAY()-4,M2<>"")``

For future reference you would do better to post data to the board using the XL2BB add-in, rather than an image.
Hello Fluff,

Thank you so much for the extremely fast response! You're always there for me

I tried your formula, but it doesn't seem to work?

Cell G1 is still red, not yellow

#### AlanY

##### Well-known Member
your 2 conditions in Cols A &B

Book1
ABC
112323/10/2020
221/10/2020
345619/10/2020
417/10/2020
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BExpression=AND(TODAY()-C1,NOT(ISBLANK(B1)))textNO
A:AExpression=NOT(ISBLANK(B1))textNO

#### Fluff

##### MrExcel MVP, Moderator

You need to select new rule & use a formula.

#### Fluff

##### MrExcel MVP, Moderator
Note ISBLANK will fail if the cell contains a formula & (IMO) is therefore best avoided.

#### David77

##### Board Regular

your 2 conditions in Cols A &B

Book1
ABC
112323/10/2020
221/10/2020
345619/10/2020
417/10/2020
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BExpression=AND(TODAY()-C1,NOT(ISBLANK(B1)))textNO
A:AExpression=NOT(ISBLANK(B1))textNO
hello thank you!

the formula works, but the one for the date

#### David77

##### Board Regular
hel
You need to select new rule & use a formula.
hello Fluff,

I used Alan's formula for the yellow highlight, but i still need the date highlight if it's been 4 days since the good was received.

Can you help?

#### David77

##### Board Regular
your 2 conditions in Cols A &B

Book1
ABC
112323/10/2020
221/10/2020
345619/10/2020
417/10/2020
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BExpression=AND(TODAY()-C1,NOT(ISBLANK(B1)))textNO
A:AExpression=NOT(ISBLANK(B1))textNO

If I type in "4" in cell O2 (4 days) and type in 24-10-2020 in cell L2 (so it's been received today), it still highlights it with a red color. It shouldnt light cell O2 with a red color before it's been 4 days. So if the current date is 24-10-2020, the cell L2 shouldnt be red before we reach the date of 28-10-2020 because it's 24-10-2020 + 4 from cell 02 = 28-10-2020.

Does that make sense? Can you help me with this please? Thank you!

#### David77

##### Board Regular
By the way, if you want more photos or information please let me know guys!

Replies
16
Views
92
Replies
3
Views
96
Replies
12
Views
334
Replies
3
Views
87
Replies
5
Views
104