Conditional Formatting to change colour of one cell in a range depending on sum of range

[rugby_nut]

Hi everyone,

I've not used the forum for some time now but I'm stuck with this Conditional Formatting (CF) problem. Hoping you guys can help.

I have a range in a row for AM, PM and NTS shifts across the days of the week, the user inputs the relevant shift score into each day. Depending on whether the shift target is met (qty in another cell), I have added CF to the range, so each cell will turn red if target not met and green if it is. So 400 for this example.
My problem is that when the overall schedule target (2200 in this example) is met, the quantity for that shift is very often short of the shift target, so the cell turns red. But I want to show this instance by highlighting the cell blue, to indicate the overall target has been met and there's no red cell. So where the last NTS shift has 225, this is short of the shift target so turns red, but I want this cell to turn blue as the overall schedule of 2200 has been achieved. The overall schedule is not always made to the exact quantity, it can go over slightly.

Please let me know if more information is required. Thanks.

Schedule	Completed	Balance	Shift Target	AM	PM	NTS	AM	PM	NTS
2200	3000	0	400	410	405	350	405	405	232

 

[etaf]

is this what you mean

3 rules , 1 for RED / Green and Blue
SO if the shift sum as entered from left to right is equal to the shedule 2200 , then it turns Blue, with RED Text to show its under the target itself , but the total shifts met the overall

Book15
	A	B	C	D	E	F	G	H	I	J
1	Schedule	Completed	Balance	Shift Target	AM	PM	NTS	AM	PM	NTS
2	2200	3000	0	400	410	405	350	405	405	232
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:J2	Expression	=SUM($E2:E2)>=$A$2	text	NO
E2:J2	Expression	=E2>=$D2	text	NO
A2	Expression	=SUM($E$2:$J$2)<A2	text	NO
E2:J2	Expression	=E2<$D2	text	NO

 

[rugby_nut]

That's perfect! Thank you very much.

 

[etaf]

you are welcome