Conditional formatting in Excel for blank values & dates

[David77]

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

 

[AlanY]

would you post a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest August 2020 v 1.2.8 )

 

[David77]

would you post a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest August 2020 v 1.2.8 )

Hello Alan,

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

BR
David

 

[Fluff]

He means to use the XL2BB add-in to post some sample data to the board, as he did in post#4.

 

[AlanY]

He means to use the XL2BB add-in to post some sample data to the board, as he did in post#4.

thanks

 

[AlanY]

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
	G					L	M	N	O
1	10					16/10/2020	12345		4
2	20					26/10/2020	12345		4
3	30					09/10/2020	12345		10
4	40					09/10/2020	12345		20
5	50					09/10/2020			8
6	60					09/10/2020	12345		18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Expression	=LEN(M1)>0	text	NO
L:L	Expression	=AND(L1+O1<TODAY(),LEN(M1)>0,LEN(O1)>0)	text	NO

 

[David77]

have a look if this works for you

Book1
	G					L	M	N	O
1	10					16/10/2020	12345		4
2	20					26/10/2020	12345		4
3	30					09/10/2020	12345		10
4	40					09/10/2020	12345		20
5	50					09/10/2020			8
6	60					09/10/2020	12345		18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Expression	=LEN(M1)>0	text	NO
L:L	Expression	=AND(L1+O1<TODAY(),LEN(M1)>0,LEN(O1)>0)	text	NO

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:

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]

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]

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]

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]

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

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