Conditional formatting using formulas

[Lmaonade]

Hi, I'm having an issue trying to use conditional formatting on a selection of data.

The only way I seem to be able to set up the conditional formatting at the moment is by doing it row by row, which is taking a long time.

Is there anyway to set it up to have just a couple of rules for the entire selection of data?

	A	B	C	D	E	F	G	H	I
1	Target	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8
2	400	300	241	433	510	402	382	375	472
3	2100	1800	2200	1993	2401	2200	3212	1931	2346
4	152	160	150	153	135	147	160	173	144
5	15	13	13	14	16	19	14	22	11

<tbody>
</tbody>

So for this example if I want any of the data in cells B2 to I5 are higher than their corresponding targets I'd want the cell Green, and if lower then red. So the result will look like above.

My data I have probably about 35 rows so I'd really rather not do this individually for each row.

Thanks,
Andy

 

[steve the fish]

These two should do it:

=AND(B2<>"",B2<$A2)
=AND(B2<>"",B2>$A2)

Apply to eg B2:I100

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ditional-formatting-question.html#post5119056

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).