Conditional Formatting

  • Thread starter Thread starter BrettOlbrys
  • Start date Start date
B

BrettOlbrys

I'm still trying to figure out this Conditional Formatting to produce
the result I'm looking for, but so far, no such luck.

Please explain, in the following formula what does the =B2 do?

=B2=MIN($B2:$E2)

And in this formula, what do I need to input where it says column()?

=(COLUMN()-1)=MATCH(MIN($B2:$E2),$B2:$E2,0)

As I said earlier, I'm trying to have Excel look at a row of data and
then put borders around the cell that has the lowest value in that row.
Using the two above formulas has not helped me produce the result.

Thanks
 
If you select B2:E2 with B2 as the active cell, use conditional formatting
then the formula will return TRUE for the lowest value in the range, thus
trigging
the conditional formatting. You must make sure you use relative references
or else
it will only look at cell B2 and not the whole range. So basically, select
B2:E2 starting from
B2, then do format>conditional formatting, formula is and paste in the below
formula,
then click formatting, select borders, click OK twice.. Don't worry about
the second formula,
formula one should take care of it..
 
Hi Brett,
To go into this a bit deeper, I have a web page on
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

The purpose of Conditional Formatting formula is to return True (zero),
or False (not zero). If the condition is true then you get the C.F. that
you assigned. Only the first C.F. that has true applies, and you have a
limit of 3 C.F. per cell (three wishes, but you only get one of them).

so your =B2=MIN($B2:$E2)
is comparing B2 to the minimum value of cells $B2:$E2 (all are on row 2).
If it is equal then you have a TRUE condition.

The first formula is a formula that you use with B2 as the active
cell when you create your conditional formatting. All the cells
in the selection will each receive a conditional formatting
formula based on how the formula appears relative to the active cell.

So the generated conditional formatting actually has
B2: =B2=MIN($B2:$E2) formula you assigned
C2: =C2=MIN($B2:$E2) formula adjusted to the right by one cell
D2: =D2=MIN($B2:$E2) formula adjusted to the right by two cells
If you include additional Rows then you might also have
B3: =B3=MIN($B3:$D3) /et cetera/
if your selection were the entire sheet, you would also have
A1: =A1=MIN($B1:$E1)
A2: =A1=MIN($B2:$E2)

If the generation of the formula does not make sense try
looking at my fill-handle web page fillhand.htm

So use the selection as the cells you want to change the color of
in your case, the border of which is on the patterns tab,
and make the formula relative to the active cell.

I don't see how the second formula would help you.
Where did you run across it related to Conditional Formatting.

BTW, your question is propagated to the Excel newsgroups where it is
actually answered. If you are not behind a corporate firewall you can
post your question directly to the Excel newsgroups and probably get
faster responses.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top