Multiple Conditional Formatting

[Vishaal]

Hi, we have the following sheet where we want to use multiple conditional formatting

Sheet is

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Order		Date	P Name	TG	Qty		Recd. Date	Recd. Qty
2	4/27/10	876
3	4/27/10	500		27/04/2010	TEST 1	29/4/2010	550		4/27/2010	500
4	5/9/10	200		09/05/2010	TEST 2	16/5/2010	200		5/20/2010	150
5	5/9/10	150		09/05/2010	TEST 3	16/5/2010	150		5/17/2010	150
6	5/10/10	600		10/05/2010	TEST 4	17/5/2010	600
7	5/10/10	254		10/05/2010	TEST 5	17/5/2010	254		5/16/2010	254
8	5/12/10	654		12/05/2010	TEST 6	19/5/2010	654		5/18/2010	654
9	5/12/10	951		12/05/2010	TEST 7	19/5/2010	951		06/10/2010	950
10	5/12/10	757		12/05/2010	TEST 8	19/5/2010	757		6/15/2010	757
Sheet2

first conditional formatting Result Sheet
D2:G2 will be yellow if there is delay or no entry (10 minute delay) and no colour when entry done and no color if get the entry in D2 and I2

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Order		Date	P Name	TG	Qty		Recd. Date	Recd. Qty
2	4/27/10	876
3	4/27/10	500		27/04/2010	TEST 1	29/4/2010	550		4/27/2010	500
4	5/9/10	200		09/05/2010	TEST 2	16/5/2010	200		5/20/2010	150
5	5/9/10	150		09/05/2010	TEST 3	16/5/2010	150		5/17/2010	150
Sheet2

Second conditional formatting result sheet
I2:J2 will be green if F2 is equal to todays date or small then today and no color if entry done in I2

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Order		Date	P Name	TG	Qty		Recd. Date	Recd. Qty
2	4/27/10	876		27/04/2010	TEST 1	29/4/2010	876				Today is 29/04/2010
3	4/27/10	500		27/04/2010	TEST 1	29/4/2010	550		4/27/2010	500
Sheet2

Third Conditional Formatting result sheet
if in coloumn B and G is there is difference in quantity then it will light blue

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Order		Date	P Name	TG	Qty		Recd. Date	Recd. Qty
2	4/27/10	876		27/04/2010	TEST 1	29/4/2010	876				Today is 29/04/2010
3	4/27/10	500		27/04/2010	TEST 1	29/4/2010	550		4/27/2010	500
Sheet2

 

[PeteWright]

I need a little clarification:

1. Condition
- What do you mean by "delay", you have so many date cells, how is the "delay" calculated?
- "no color" is the default state and doesn't need to be formatted. Correct me if I'm wrong.

For 2. and 3. Condition see Mini Sheet below:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Order		Date	P Name	TG	Qty		Recd. Date	Recd. Qty
2	04/27/2010	876
3	04/27/2010	500		04/27/2010	TEST 1	04/29/2010	550		04/27/2010	500
4	05/09/2010	200		05/09/2010	TEST 2	05/16/2010	200		05/20/2010	150
5	05/09/2010	150		05/09/2010	TEST 3	05/16/2010	150		05/17/2010	150
6	05/10/2010	600		05/10/2010	TEST 4	05/17/2010	600
7	05/10/2010	254		05/10/2010	TEST 5	05/17/2010	254		05/16/2010	254
8	05/12/2010	654		05/12/2010	TEST 6	05/19/2010	654		05/18/2010	654
9	05/12/2010	951		05/12/2010	TEST 7	05/19/2010	951		10/06/2010	950
10	05/12/2010	757		05/12/2010	TEST 8	05/19/2010	757		06/15/2010	757
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J2	Expression	= NOT( ISBLANK( F2 ) )	text	NO
I2:J2	Expression	= E2 <= TODAY()	text	NO
B2:B10,G2:G10	Expression	= $B2<>$G2	text	NO

 

[Vishaal]

you are right

in the first condtion

D2:G2 will be yellow if there is delay or no entry in D2 (we will check if D2 is not filled with in 10 minute after getting the entry in A2) and no colour when entry done and no color if get the entry in D2 and I2

 

[PeteWright]

Hi,
unfortunately you didn't answer my question, instead you posted the same answer.

you are right

in the first condtion

D2:G2 will be yellow if there is delay or no entry in D2 (we will check if D2 is not filled with in 10 minute after getting the entry in A2) and no colour when entry done and no color if get the entry in D2 and I2

Hi, we have the following sheet where we want to use multiple conditional formatting


first conditional formatting Result Sheet
D2:G2 will be yellow if there is delay or no entry (10 minute delay) and no colour when entry done and no color if get the entry in D2 and I2

If I should help, I need more information from you.
Please tell me, how is the first condition calculated.