Maintaining Number Colors

[TELCO1]

I have columns set up in the following fashion (an example):

Column A B C D E F G

Product Price Price Price Best 2nd Best 3rd Best
Price Price Price

Columns B, C, and D are supplier prices for the products listed under
column A. Each supplier is color coded, so all the prices listed under
column B is green, C is Red, D is blue (as an example).

Columns E, F, and G are set up using the "SMALL" formula to rank the
best price to the worst price. When I get a pricing update from each
supplier each month, the numbers in columns E-F obviously change.

Right now, the numbers in columns E-F are black. I have to manually
change the colors to match the suppliers. Is there any way of fixing the
color of the numbers? In other words, if B's price (green) was in column
F, but next month it moves to column E, how do I maintain the green color.

I know that cutting and pasting maintains the cell font and color, but
this is not what I want to do. With allot of suppliers and products, you
can imagine how time consuming it is to re-color Columns E-F every month.

Hope you can follow this,

Thanks in advance,

Lawrie

 

[JulieD]

Hi

use can use conditional formatting for this

select the data in columns E through G
choose format / conditional formatting
choose
formula is
type
=INDEX($B$1:$E2,1,MATCH(E2,$B2:$D2,0))="Supplier A"
click on the format button, set the appropriate formatting (BTW you need the
$ only where they are)
OK
click on ADD
formula is
type
=INDEX($B$1:$E2,1,MATCH(E2,$B2:$D2,0))="Supplier B"
click on the format button, set the appropriate formatting
OK
click on ADD
formula is
type
=INDEX($B$1:$E2,1,MATCH(E2,$B2:$D2,0))="Supplier C"
click on the format button, set the appropriate formatting
OK twice

Hope this helps
Cheers
JulieD

 

[TELCO1]

That's great Julie. I'm going to give it a go.

Thanks,

Lawrie