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: