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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
He means to use the XL2BB add-in to post some sample data to the board, as he did in post#4.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Hello Alan,

I am not quite sure what you mean, could you please elaborate?
What kind of screenshot?

BR
David
have a look if this works for you

Book1
GLMNO
11016/10/2020123454
22026/10/2020123454
33009/10/20201234510
44009/10/20201234520
55009/10/20208
66009/10/20201234518
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GExpression=LEN(M1)>0textNO
L:LExpression=AND(L1+O1<TODAY(),LEN(M1)>0,LEN(O1)>0)textNO
 
Solution

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
have a look if this works for you

Book1
GLMNO
11016/10/2020123454
22026/10/2020123454
33009/10/20201234510
44009/10/20201234520
55009/10/20208
66009/10/20201234518
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GExpression=LEN(M1)>0textNO
L:LExpression=AND(L1+O1<TODAY(),LEN(M1)>0,LEN(O1)>0)textNO
Hello Alan,

I apologize for my late response, but truly appreciate your assistance (a lot!!!)

It definitely looks better now, but unfortunately it seems like the conditional formatting still does not work for me:

1604493270414.png


So right now it's showing some errors. For example, cell M2 should clearly be highlighted in red since it's been over 4 days since the good was ordered (2020-10-16 + 4 = 2020-10-20, which is around 15 days ago - and the number in cell N2 is also still there).

Likewise, it is highlighting some blank cells with a red color for some reason, which it should not be doing, these should just be ignored.

Can you help me out with solving this issue? I apologize for taking so much of your time, I literally cannot express how much I appreciate it!! :)

Best regards,
David
 

David77

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

ADVERTISEMENT

He means to use the XL2BB add-in to post some sample data to the board, as he did in post#4.
So sorry Fluff, my technical language is still very limited. What is an "XL2BB add-in"?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
It's an add-in that allows you to copy usable data to the board, click the XL2BB link in the reply window for more information.

In conditional formatting the formula needs to match the first row in the applies to range, so you need to change the formula to look at M2, P2 etc.
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
It's an add-in that allows you to copy usable data to the board, click the XL2BB link in the reply window for more information.

In conditional formatting the formula needs to match the first row in the applies to range, so you need to change the formula to look at M2, P2 etc.
It's an add-in that allows you to copy usable data to the board, click the XL2BB link in the reply window for more information.

In conditional formatting the formula needs to match the first row in the applies to range, so you need to change the formula to look at M2, P2 etc.
Hello Fluff,

Unfortunately I do not have that add-in available to me :(
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
It's an add-in that allows you to copy usable data to the board, click the XL2BB link in the reply window for more information.

In conditional formatting the formula needs to match the first row in the applies to range, so you need to change the formula to look at M2, P2 etc.
Fluff,

You're an excel god incarnated haha :) I apologize for the mistake, you were right and it works now!!! :)

One final thing, then I shall promise not to bother you again :)

1604660667327.png


Is it possible to edit the formula so it also includes a final rule that: If the cell in Column O says "YES", then the cell in Column M (corresponding to the same cell number in column O) should not be highlighted with a red color and remain white.

I would really appreciate your final input/solution to this. Thank you so very much for your time! Both you and AlanY :)

Best regards,
David
 

Watch MrExcel Video

Forum statistics

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