Conditional Format a column based on another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Column A has 100 entries of either 1,2 or 3 which are not necessarily sorted
in order depending upon sorting in other columns but I want the minimum value
in column B based on column A to remain conditionally formatted. i.e. Column
B will always have three cells highlighted.
 
What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values. With
a limit of three values in Column A, there is then three values to highlight
in Column B.
 
If I read it correctly, you want to highlight the minimum value in B where A
=1, then where A=2, etc.

If so, I would add 3 formulae to say M1:M3, of

=MIN(IF($A$1:$A$30=ROW(A1),$B$1:$B$30))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Then in the CF, use a formula of

=OR(AND(A1=1,B1=$M$1),AND(A1=2,B1=$M$2),AND(A1=3,B1=$M$3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks Bob but not quite;

Assume for the exercise the following values are in column A:
1,1,1,3,3,2,1,2,3,2
and these values are in column B:
10*,12,13,14,13*,12,14,11*,15,14 with * indicating which should be
highlighted as they are the minimum for the group in the corresponding column
A.
 
Hey, guess what, that is exactly what my solution comes up with!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top