6

given a range of non adjacent columns (not sure if it matters if they're adjacent) i.e

F1:F100,J1:J100,N1:N100

for each row i'd like to highlight the cell with the maximum value.

i tried specifying the range as stated above and using the following custom formula:

=F1>=MAX(F1,J1,N1)

but that doesn't work as i expected, i guess what i'm missing is how to reference the 'current' cell in the left hand side of the inequality.

Improve this question
1
  • Not exactly the answer you're looking for it was what I actually needed: the right tab "Color Scale" in the "Conditional Formatting" pane allows to visualize quickly the highest value in a range.
    – wip
    Commented Sep 28, 2017 at 15:19

1 Answer 1

Reset to default
8

In a quick test, I was able to accomplish what it sounds like you are wanting with the following.

  1. Using CTRL + Left Mouse Click, Highlight one rows worth of cells that you want included in the validation; F, J, and N in your example.
  2. Go to the Format menu and choose Conditional Formatting
  3. In the Conditional format rules pane, change the "Format cells if..." dropdown to "Custom formula is"
  4. In the formula field, put:

=($A1:$E1)=max($A1:$A1,$C1:$C1,$E1:$E1)

where A and E columns would be your extreme left and right columns (F & N in your example). Don't worry about the columns between because you aren't applying the rule to those other fields, and also aren't including them in your MAX() calculation

  1. Change the "Apply to range" as far down your columns as you require (screenshot below)
  2. Style as you see fit and click Done

enter image description here enter image description here enter image description here

On this last screenshot, I'm showing that I went ahead and made my column range huge, but as soon as I filled in data on row 22, it picked up the max value from columns A, C, E, and formatted those columns accordingly.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .