Conditional formatting based on max in range of other columns

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

If I have a list oif names in the A column, and various #s in columns B thru
R, and the max of those #'s is in B20. Can I conditional format any A column
name that has the max # (14) in it's row. Basically I'd like to conditional
format the names Steve and Billy because they're the only ones that have a 14
in their row.

Thanks,

A B ..... Q R
Steve 13 14 11
Rick 13 7 10
Billy 12 14 9
Kurt 13 7 9
Dan 12 6 13

max 14
 
1. Select the cell/Range.Column A.
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=COUNTIF(B1:R1,MAX(B:R))

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: IF the initial selection is not entire column A and say A2:A10; then the
formula would be =COUNTIF(B2:R2,MAX($B$2:$R$10))

If this post helps click Yes
 
Perfect. Thank you very much.

Now, I have the identical range of names on another sheet, but not the
numbers. I'm trying to conditional format those names the same way referring
to the max on the other sheet, but I can't seem to get it to work. I'm trying
to add the sheet ref in front of the formula like this,
=sheet1!COUNTIF(B2:R2,MAX($B$2:$R$10)), but it's not working. Can conditional
formatting reference to other sheets ?

Thanks again,

Steve
 
Back
Top