Conditional Formatting

[A Parker]

How do I conditionally format cells, which are NOT in a range. I'm looking to highlight the highest and lowest value in random cells.

 

[Peter_SSs]

In case you may have (now or in the future) more columns than just D:K you could also use something like this which, although a little longer, is easily expandable since it is applied to the whole range (D:K in this case but could just as easily be D:Z or beyond), not just every second cell.

23 09 08.xlsm
	D	E	F	G	H	I	J	K
3	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
4	500	1,000,000	155.25	310,500	480.00	960,000	130.00	260,000
5
6			155.25	310,500	480.00	960,000
CF MAX MIN (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:K6	Expression	=AND(D$3="Amount",D4<>"",D4=MIN(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4))))	text	NO
D4:K6	Expression	=AND(D$3="Amount",D4<>"",D4=MAX(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4))))	text	NO

 

[A Parker]

In case you may have (now or in the future) more columns than just D:K you could also use something like this which, although a little longer, is easily expandable since it is applied to the whole range (D:K in this case but could just as easily be D:Z or beyond), not just every second cell.

23 09 08.xlsm
	D	E	F	G	H	I	J	K
3	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
4	500	1,000,000	155.25	310,500	480.00	960,000	130.00	260,000
5
6			155.25	310,500	480.00	960,000
CF MAX MIN (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:K6	Expression	=AND(D$3="Amount",D4<>"",D4=MIN(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4))))	text	NO
D4:K6	Expression	=AND(D$3="Amount",D4<>"",D4=MAX(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4))))	text	NO

Thanks again.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.