Solved Excel Conditional Formatting

Microsoft Office excel 2007 - upgrade
July 21, 2011 at 11:33:12
Specs: Windows Vista
Hey,
I'm trying to compare different suppliers with Excel. The columns stand for suppliers and the rows for products. I want to use conditional formatting in a way that in every row the lowest price will be marked. I'm able do to this for one row, but when I try to apply the formatting in each row by dragging, it marks the lowest price in the whole range! How can I make this work for each and every line?

Thanks,
Al


See More: Excel Conditional Formatting

Report •


✔ Best Answer
July 23, 2011 at 12:04:08
I had no problem doing what you asked.

I started with this table:

	
    A    B     C    D    E
1        S1    S2   S3   S4
2   P1   4     3    2    1
3   P2   1     2    1    3
4   P3   4     1    3    2
5   P4   1     2    3    4

1 - Select B2:E2
2 - Home Ribbon....Conditional Formatting
3 - New Rule
4 - Use a Formula...
5 - =B2=MIN($B2:$E2) (Note the use of the $ for the columns only)
6 - Format...Fill...Green
7 - OK...OK
8 - With B2:E2 still selected, click Format Painter
9 - Drag your mouse over B3:E5 and release

The 1's in each row were shaded in Green.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 22, 2011 at 03:49:14
If you have 7 columns, therefore 7 suppliers do 7 conditional formats one for each column.

OR

Insert a row above and use the MIN function to show the lowest price of that column.

Hope that helps
K


Report •

#2
July 22, 2011 at 11:01:29
in your conditional formating formula, make sure you have the $ in the right place.

you can use =A1=MIN(a1:f1) as your conditional check, and apply the formating when its true. if you are using excel 2007 you can then apply this condition to a variety of row. Important. When you apply the first condition formating make sure you have the appropriate cells selected and the the appropriate one activated. In this case you would need to select A1 to F1 with A1 being the active cell of the selection set.


Report •

#3
July 23, 2011 at 11:04:36
Thanks for your reply. I know this option, and though it solves my problem, there must be a more elegant way of doing this. I tried using the following format condition for row #2: "display the cell in green when the cell is ranked bottom". The option is available in excel. I applied this setting on cells B2:B6 but it keeps adding $ signs.

Report •

Related Solutions

#4
July 23, 2011 at 12:04:08
✔ Best Answer
I had no problem doing what you asked.

I started with this table:

	
    A    B     C    D    E
1        S1    S2   S3   S4
2   P1   4     3    2    1
3   P2   1     2    1    3
4   P3   4     1    3    2
5   P4   1     2    3    4

1 - Select B2:E2
2 - Home Ribbon....Conditional Formatting
3 - New Rule
4 - Use a Formula...
5 - =B2=MIN($B2:$E2) (Note the use of the $ for the columns only)
6 - Format...Fill...Green
7 - OK...OK
8 - With B2:E2 still selected, click Format Painter
9 - Drag your mouse over B3:E5 and release

The 1's in each row were shaded in Green.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 23, 2011 at 12:15:56
awesome... thanks a lot!

Report •

Ask Question