Conditional formatting in an array

[Plongy]

I'm trying to format rows in an array, simply to colour code the priority.
In Column 'A' I am prioritising a list of tasks by designating it '1' to '3' depending on importance. Based on this I want the remaining 5 cells in an individual row of the array to appear with a red, amber or blue fill, then once the task is completed, column A will be annotated with a 'C' and go green.
I'm thinking I need to do four rules, one for each colour? or do I?

 

[jdellasala]

Arrays are treated just like cell data. Mike Garvin at ExcelIsFun explained it best. To work out what your formula has to be, make a mirror of the data using a logical formula.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Individual Cell CF									Whole Row CF
2	Max:	90								Max:	76
3	Array	46	9	55		FALSE	FALSE	FALSE		Array	31	75	58		FALSE	FALSE	FALSE
4		72	47	26		FALSE	FALSE	FALSE			39	26	65		FALSE	FALSE	FALSE
5		83	17	59		FALSE	FALSE	FALSE			49	46	45		FALSE	FALSE	FALSE
6		89	29	54		FALSE	FALSE	FALSE			22	39	84		FALSE	FALSE	FALSE
7		22	13	89		FALSE	FALSE	FALSE			63	22	62		FALSE	FALSE	FALSE
8		44	88	52		FALSE	FALSE	FALSE			31	89	54		FALSE	FALSE	FALSE
9		77	34	76		FALSE	FALSE	FALSE			41	55	11		FALSE	FALSE	FALSE
10		100	48	5		TRUE	FALSE	FALSE			43	99	28		FALSE	FALSE	FALSE
11		25	64	5		FALSE	FALSE	FALSE			5	4	33		FALSE	FALSE	FALSE
12		66	87	30		FALSE	FALSE	FALSE			38	46	58		FALSE	FALSE	FALSE
13		87	31	93		FALSE	FALSE	TRUE			35	73	67		FALSE	FALSE	FALSE
14		2	36	32		FALSE	FALSE	FALSE			41	55	15		FALSE	FALSE	FALSE
15		96	87	38		TRUE	FALSE	FALSE			80	44	66		TRUE	TRUE	TRUE
16		47	87	20		FALSE	FALSE	FALSE			68	81	93		FALSE	FALSE	FALSE
17		9	14	6		FALSE	FALSE	FALSE			86	97	97		TRUE	TRUE	TRUE
18		37	7	31		FALSE	FALSE	FALSE			41	10	77		FALSE	FALSE	FALSE
19		71	56	18		FALSE	FALSE	FALSE			67	18	49		FALSE	FALSE	FALSE
20		96	25	13		TRUE	FALSE	FALSE			54	55	22		FALSE	FALSE	FALSE
21		72	15	42		FALSE	FALSE	FALSE			85	16	66		TRUE	TRUE	TRUE
22		43	21	28		FALSE	FALSE	FALSE			16	16	89		FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
B2,K2	B2	=MAX(B3#)-10
K3:M22,B3:D22	B3	=RANDARRAY(20,,1,100,1)
F3:H22	F3	=B3>=$B$2
O3:Q22	O3	=$K3>=$K$2
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:M22	Expression	=$K3>=$K$2	text	NO
B3:D22	Expression	=B3>=$B$2	text	NO
F3:H22,O3:Q22	Cell Value	=TRUE	text	NO

The formula for conditional formatting is worked out in cells F3 and O3, and then used for the conditional formatting. Remember to always start the conditional formatting range at the TOP LEFT of the data, and the "perspective" of the formula needed is from that vantage point.

A playlist of 138 (!) short videos (with sample files) can be found at Excel Conditional Formatting Tricks.