Conditional Formatting with reference to other cells and with multiple rules (in the table)

[Monikiy]

Good time of the day to you all!

I have a problem with conditional formatting and really hope that someone could help

In the below table the formatting needs to be applied to the columns D, E, F
and it should be based on the values in columns A & C with the following condition:

• if cell D4 >= cell A4, cell D4 needs to turn green
• if cell D4 <= cell C4, cell D4 needs to turn red
• if cell D4 =AND(D4<A4, D4>C4), cell D4 needs to turn yellow

These 3 rules should be applied to all cells in columns D, E, F. However when I select for example column D4:D17 and set the above rules in the conditional formatting section, the colors are applied in the end, but only green and red, and it is also showing wrong colors.

I feel like I am missing something, an additional condition maybe or the order of the rules... I have tried to tweak the formulas, use $$ on the cells from columns A & C, but nothing seems to work.

Below I pasted a photo of the table and under it also an actual table, just for the reference.

I would really appreciate any help, and please tell me if I didn't explain myself well and I will try again.

Target & Limits (per month)​	Target & Limits (per month)​	Target & Limits (per month)​	Results​	Results​	Results​
>=100%​	80-99%​	<=79%​	month 1​	month 2​	month 3​
350	298	277	410	498	517
78	67	62	79	81	80
233	198	184	210	193	188
6700	5695	5293	5813	5879	5608
44	38	35	17	21	23
142	120	112	121	128	112
300100	255085	237079	311202	310040	308594
8850	7523	6992	8598	8591	8618
45%	38%	36%	40%	38%	41%
60%	51%	47%	60%	66%	65%
125033	106278	98776	369476	370906	370519
3033	2578	2396	8668	8726	8593
86700	73695	68493	93830	101437	75552
1867	1587	1475	1609	3072	1528

Many thanks in advance!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	Target & Limits (per month)			Results	Results	Results
2	>=100%	80-99%	<=79%	month 1	month 2	month 3
3
4	350	298	277	410	498	517
5	78	67	62	79	81	80
6	233	198	184	210	193	188
7	6700	5695	5293	5813	5879	5608
8	44	38	35	17	21	23
9	142	120	112	121	128	112
10	300100	255085	237079	311202	310040	308594
11	8850	7523	6992	8598	8591	8618
12	45%	38%	36%	40%	38%	41%
13	60%	51%	47%	60%	66%	65%
14	125033	106278	98776	369476	370906	370519
15	3033	2578	2396	8668	8726	8593
16	86700	73695	68493	93830	101437	75552
17	1867	1587	1475	1609	3072	1528
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:F17	Expression	=AND(D4<$A4,D4>$C4)	text	NO
D4:F17	Expression	=D4<=$C4	text	NO
D4:F17	Expression	=D4>=$A4	text	NO

 

[Fluff]

Cross posted Conditional Formatting with reference to other cells and with multiple rules(in the table)

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Monikiy]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	Target & Limits (per month)			Results	Results	Results
2	>=100%	80-99%	<=79%	month 1	month 2	month 3
3
4	350	298	277	410	498	517
5	78	67	62	79	81	80
6	233	198	184	210	193	188
7	6700	5695	5293	5813	5879	5608
8	44	38	35	17	21	23
9	142	120	112	121	128	112
10	300100	255085	237079	311202	310040	308594
11	8850	7523	6992	8598	8591	8618
12	45%	38%	36%	40%	38%	41%
13	60%	51%	47%	60%	66%	65%
14	125033	106278	98776	369476	370906	370519
15	3033	2578	2396	8668	8726	8593
16	86700	73695	68493	93830	101437	75552
17	1867	1587	1475	1609	3072	1528
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:F17	Expression	=AND(D4<$A4,D4>$C4)	text	NO
D4:F17	Expression	=D4<=$C4	text	NO
D4:F17	Expression	=D4>=$A4	text	NO

Thank you very much! Everything magically works now with your formulas

 

[Fluff]

You're welcome & thanks for the feedback.