Highlight lowest 3 values in an array

[noahsmom]

Tried the following & several other ways:

Using conditional formatting - Cell Value is Equal To...

Condition 1
=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)
Highlight yellow
Condition 2
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),2)
Highlight green
Condition 3
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),3)
Highlight blue

gives me the following error:
You may not use unions, intersection, or array constants for conditional formatting criteria.


Based upon a spreadsheet that looks like this...

Sample.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	(Identifying					Vendor	123
2	Information)					Contact	Jane
3						Quote #	1A2B
4						Valid	12/31
5
6	Item#	ItemDesc	Qty	UOM	UnitCost	ExtCost	Bid	ExtBid	Terms	Delivery	Freight	FOB	StdPkg	MinBuy
7	226801	ABC	2	ea	1.55	3.10	1.60	3.20		7	Add		5	5
8	226802	DEF	11	ea	1.51	16.61	1.57	17.27		7	Add		5	5
9	226803	HIJ	2	ea	1.56	3.12	1.61	3.22		7	Add		5	5
10	226807	KLM	4	ea	2.36	9.44	2.40	9.60		7	Add		5	5
11	226808	NOP	6	ea	0.28	1.68	0.32	1.92		7	Add		5	5
12	226809	QRS	2	ea	1.69	3.38	1.73	3.46		7	Add		5	5
13	226810	TUV	1	ea	2.04	2.04
14	244500	WXYZ	5	ea	10.23	51.15	10.27	51.35		7	Add		5	5
Sample

columns G thru N may be repeated numerous times (different vendors). I need the lowest 3 values under the EXT COST column excluding the blank cells. The array will always remain the same (H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF,EN).

Since conditional formatting doesn't seem to be doing the trick, does anyone have any additional ideas? Possibly a macro?

I hope that I have provided enough information. ANY help would be more than greatly appreciated!!!!

Thanks,
Noahsmom ray:

 

[GlennUK]

Use these

Hi,

use these. ( we had a mistake in the formula for Condition 1 too ) ...
Condition 1
Formula Is ...

=H7=MIN($H7,$P7,$X7,$AF7,$AN7,$AV7,$BD7,$BL7,$BT7,$CB7,$CJ7,$CR7,$CZ7,$DH7,$DP7,$DX7,$EF7,$EN7)

Condition 2
Formula Is ...

=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,IF($H$7:$EN$7<>"",$H$7:$EN$7,9999)),2)

Condition 3
Formula Is ...

=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,IF($H$7:$EN$7<>"",$H$7:$EN$7,9999)),3)

Good Luck!

 

[noahsmom]

It Worked!!! Yippee!!! Hip Hip Hooray!!!!

Thanks tremendously GlennUK!!! You’ve helped me save so much time!

Would you mind telling me how to copy this conditional formatting to the remaining rows?

 

[GlennUK]

Do a copy and Edit/Paste Special/Formats

 

[noahsmom]

Please see new post.