Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Maintaining Number Colors
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 |
#2
|
|||
|
|||
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" wrote in message . .. 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 |
#3
|
|||
|
|||
That's great Julie. I'm going to give it a go.
Thanks, Lawrie JulieD wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
multiply by actual number in cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |