Conditional Formatting - based on a given cell reference and a given range of cells from that cell reference

[tstout1000]

I have an array (example below). I want to be able to highlight via conditional formatting using a Cell Reference (below N3) and range (below 5).

I have inputs that I can change the Cell Reference and Range, Example: Cell Reference = Q3 and Range = 9.

I can easily highlight the ending cell using format using a formula =CELL("address",B2)=$D$4 with $D$4 being the location of my input (e.g. Q3).
But I also want to highlight the preceding values dynamically (e.g. above the 8 previous columns).
Any help greatly appreciated.

 

[Momentman]

A long winded formula, but should get you what you want

Rename First worksheet Folder.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17		Address	CellCount
2	a	191	186	180	223	108	12	179	72	52	130	97	188	21	207	49	85	127		$N$3	6
3	b	194	179	230	35	38	131	34	2	190	127	15	109	107	121	88	3	97
4	c	243	72	192	101	202	35	79	58	187	45	7	228	156	107	56	159	163
5	d	243	95	91	108	50	214	61	133	248	107	115	107	178	56	169	101	85
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:R8	Expression	=AND(ROW(B2)=ROW(INDIRECT($T$2)),COLUMN(INDIRECT($T$2))-COLUMN(B2)>=0,COLUMN(INDIRECT($T$2))-COLUMN(B2)<$U$2)	text	NO