Conditional formatting in Excel for blank values & dates

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Hello everybody,

To make this quick, i have the following setup.

1603463566788.png


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:

1603463647982.png


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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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?

1603464347516.png


Cell G1 is still red, not yellow
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You need to select new rule & use a formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
Note ISBLANK will fail if the cell contains a formula & (IMO) is therefore best avoided.
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
1603570562483.png


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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
By the way, if you want more photos or information please let me know guys!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,021
Messages
5,545,539
Members
410,690
Latest member
navneetr
Top