Conditional formatting based on max in range of other columns

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
 
J

Jacob Skaria

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
 
S

Steve

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top