Conditional format based on min value in a range

[cboz]

In Excel. I have a range where I want to change the color of a cell that contains the minimum value in the row. I tried using a min function forumla in a new column and using the cell value equals comparison in Conditional Format but that only works for the one row. Help.

 

[SkipVought]

Hi

1) Select ALL cells you want to have conditionally formatted

2) In CF, select Formual Is:

3) In the formula, be sure that the references are RELATIVE.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[cboz]

When you say relative do you mean something like A2:N134 as apposed to $A$2:$N$134? I tried using a range such as A2:N134 but got an error message telling me I can't use a range in a conditional format formula. Then when I tried formula =min(a2:n134) it applied the formatting to every cell as apposed to the one sell with the minimum value.

I have a range N2:N134. I want to color the cell in each row that has the minumum value for that row only then do the same thing in the next row and so on through row 134.

 

[SkipVought]

From you explanation I can't determine exactly waht you want.

Let's just consider ROW 2.

1) What cells are to be conditionally formatted?

2) What is CRITERIA ONE for this format?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[byundt]

If you are using the worksheet to perform the Conditional Formatting (rather than VBA), consider a "Formula Is" formula like:
=A1=MIN($A1:$IV1)

This formula assumes that cell A1 is in the upper left end of the range of selected cells. It will find the cell(s) that contain the minimum value for that row.

To use the formula, select all the cells that you want to watch (multiple columns and rows), the open the Conditional Formatting menu item. Use "Formula Is", enter the formula (changing the A1 to the upper left cell in the selection and the row number to the first row in the selection, and the $A and $IV to the starting and ending columns in the selection.

 

[cboz]

Thanks you for puzzeling out what I was trying to say. Your solution works perfectly. I didn't know about using 2 equal signs in a formula. Is there somewhere I can get more information about how this works? Thanks again, it is a solution that I will use again and again.

 

[byundt]

cboz,
The second equals sign is part of a "Boolean" expression. It means, does A1=MIN($A1:$IV1) -- True or False?

Conditional Formatting looks at the result of this Boolean expression and, if True it applies the desired formatting. This is how the first equals sign is used.

FWIW, I used to use the clumsier (but easier to understand) IF function to do the same job:
=IF(A1=MIN($A1:$IV1),TRUE, FALSE)
Brad

 

[cboz]

I think I understand. We'll see. Thanks again for your help.