Conditional Formatting - coloring cells

[Grizzle33]

Hello everyone.

I have a spread sheet that I keep track of freight costs on. I quote four different carriers on each shipment we do, and I would like Excel to highlight the cell with the lowest cost in it. I've been doing this manually, but if it's something that can be done automatically it would be nice.

I found the conditional formatting, but it seems the rule I'm looking for might have to be created?

Thanks in advance.

 

[Yahya]

Hi try this

Excel Workbook
	A	B	C	D	E
3	Shipment 1	225	416	494	320
4	Shipment 2	227	25	103	406
5	Shipment 3	349	296	360	202
6	Shipment 4	370	287	96	29
7	Shipment 5	487	421	233	120
Sheet2

Excel 2007

in conditional format rule:

=B3=MIN($B3:$E3)

 

[Desu Nota from Columbus]

Assuming Column C has the cells with the numbers in them: Highlight The Entire Column and select Conditional Formatting (using a formula)

=C1=MIN($C$1:$C$100) set the formatting to what you want.


Change the Column and data range to match your variables

 

[Grizzle33]

Ok, I have the current code as follows:

=G2=MIN($G$2:$J$2)

It selects the lowest value in a row of four.

Any secret to dragging that formatting down to say 50 rows? Or do I need to do each one?

 

[tc88]

Ok, I have the current code as follows:

=G2=MIN($G$2:$J$2)

It selects the lowest value in a row of four.

Any secret to dragging that formatting down to say 50 rows? Or do I need to do each one?

Under Conditional Formatting-->Manage Rules

Change that to your desired range of cells, I would also absolute cell reference ($B$2:$B$51 for example)

 

[Yahya]

Ok, I have the current code as follows:

=G2=MIN($G$2:$J$2)

It selects the lowest value in a row of four.

Any secret to dragging that formatting down to say 50 rows? Or do I need to do each one?

your formula will compare just range $G$2:$J$2
not all ranges
you should change it to $G2:$J2

 

[Grizzle33]

Ok that worked fine.

Now I have the rule running down to the 50th row, I dont have data going into that row yet, and they are all colored green until I type something, and it will white out the higher ones, and keep the lowest green.

Is there anyway I can make this rule apply only when there's data in the cells?

Maybe I'll just have to edit it each time I add new data?

 

[grizle]

Just add a second condition to the conditional formula to check that there is at least one entry in the row. For example =AND(SUM($G2:$J2)<>0,G2=MIN($G2:$J2))

 

[Grizzle33]

Just add a second condition to the conditional formula to check that there is at least one entry in the row. For example =AND(SUM($G2:$J2)<>0,G2=MIN($G2:$J2))

So the complete should look something like this?

=G2=MIN($G2:$J2) =AND(SUM($G2:$J2)<>0,G2=MIN($G2:$J+$A$1:$J$16

Would that all be the same rule, or create two different rules? When I enter that above it colors everything green, but it does check for data in a cell before it colors.

 

[Desu Nota from Columbus]

use this instead:

=AND(G2=MIN($G$2:$J$2),G2<>"")