Conditional Formatting - multiple criteria

[colinheslop1984]

I am using Excel 2016

Is it possible to do conditional formatting with multiple criteria within 1 rule?

Here is what I have -

AA4 = TODAY()
E4 = Start Date, i.e 14/09/21
P4 = Data validation list, either Education, IWS or Complete

I want cells J4: P4 to colour blue where P4="Education" - I have done this
I want cells J4: P4 to colour green where P4="complete" - I have done this

Here is what I want to do -

When P4=IWS, I want cell J4 to colour yellow where E4+30<AA4 is TRUE, K4 to colour yellow where E4+60<AA4, L4 to colour yellow where E4+90<AA4 and so on.

I have done most of this, however, when I change P4 to Complete, expecting J4:P4 to turn green, only P4 is green and J4:O4 remain yellow. I understand why this has happened, but not sure what to solution is

Any help would be appreciated.

 

[Joe4]

If using Conditional Formatting formulas on a multi-cell range, you usually need to lock the column reference in your formula, or else they will shift, i.e.
if your formula includes:

E4+30<AA4

then you probably need to write it as:

$E4+30<$AA4

And the same thing for any other column references in your CF formulas.

 

[colinheslop1984]

If using Conditional Formatting formulas on a multi-cell range, you usually need to lock the column reference in your formula, or else they will shift, i.e.
if your formula includes:

E4+30<AA4

then you probably need to write it as:

$E4+30<$AA4

And the same thing for any other column references in your CF formulas.

Thanks, I do normally do that, but didn't for this example.

Still looking for a solution if anyone has any ideas

 

[Kerryx]

Book1
	E	F	G	H	I	J	K	L	M	N	O	P
4	07/03/2022											IWS
5												IWS
6												Complete
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O4	Expression	=AND($E$4+180<TODAY(),$P$4="IWS")	text	NO
N4	Expression	=AND($E$4+150<TODAY(),$P$4="IWS")	text	NO
M4	Expression	=AND($E$4+120<TODAY(),$P$4="IWS")	text	NO
L4	Expression	=AND($E$4+90<TODAY(),$P$4="IWS")	text	NO
K4	Expression	=AND($E$4+60<TODAY(),$P$4="IWS")	text	NO
J4	Expression	=AND($E$4+30<TODAY(),$P$4="IWS")	text	NO
J4:P4	Expression	=$P$4="Complete"	text	NO
J4:P4	Expression	=$P$4="Education"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
P4	List	=$R$4:$R$6
P5:P6	List	=$P$4:$P$6

 

[Joe4]

Without seeing the exact rules you have set-up, it is difficult to help you.
Can you show us an example what you currently have (data and the CF rules you have set-up), and your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[colinheslop1984]

Book1
	E	F	G	H	I	J	K	L	M	N	O	P
4	07/03/2022											IWS
5												IWS
6												Complete
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O4	Expression	=AND($E$4+180<TODAY(),$P$4="IWS")	text	NO
N4	Expression	=AND($E$4+150<TODAY(),$P$4="IWS")	text	NO
M4	Expression	=AND($E$4+120<TODAY(),$P$4="IWS")	text	NO
L4	Expression	=AND($E$4+90<TODAY(),$P$4="IWS")	text	NO
K4	Expression	=AND($E$4+60<TODAY(),$P$4="IWS")	text	NO
J4	Expression	=AND($E$4+30<TODAY(),$P$4="IWS")	text	NO
J4:P4	Expression	=$P$4="Complete"	text	NO
J4:P4	Expression	=$P$4="Education"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
P4	List	=$R$4:$R$6
P5:P6	List	=$P$4:$P$6

That worked perfectly.

If I wanted to take this to another level...

J4:JO are blank cells and your solution works just great, but if I wanted to put a date in cell J4, how could I get this to turn green but only where P4=IWS, without over ruling any of the other formatting rules

 

[Kerryx]

Try adding this as the first rule

=AND($J$4<>"",$P$4="IWS")

and colour Green
leave the other rules as they are. this is just going to change the cell J4 to green if there is a value in it.It will ignore the rule =AND($E$4+30<TODAY(),$P$4="IWS") or do yoou still need it to change to orange if the date is +30 days from today.
The cells will all turn green if Complete is selected and Blue if Education is completed. The more conditions you add may require all of these to be looked at, but the order / sequence of the formatting is important as the format is applied when the conditions are met.