Conditional Formatting - how to set range for “one condition per row”?

0
votes

I thought I understood Conditional Formatting, but I'm having trouble applying it.

I have a table like this:

J   F   M   A   M   J   J   A   S   O   N   D
317 876 781 735 490 790 569 922 976 923 259 270
440 734 788 729 337 876 641 735 892 906 463 872
773 863 537 538 389 988 404 599 459 976 235 891
772 134 845 218 669 414 352 276 965 677 723 342
343 283 251 703 420 430 355 485 537 723 838 199
354 846 503 448 196 249 237 503 309 291 432 807
259 126 213 254 577 688 195 681 658 407 637 827
293 333 562 242 504 335 276 675 173 592 388 418

And I'd just like to highlight, via Green Fill, the Max() in each row.

I highlighted my range, keeping the top left (317) as my active cell. Then I tried this formula: =max($A2:$L2), but this just highlights my entire table:

ScreenShot

If I highlight row 2, use =Max($A2:$L2), then the whole row gets highlighted...I've even tried using the Top 10 default rule, changing to top 1, but no avail.

What I can do, is use the Top 1, on a single row, then paste that formatting down. However, I'd like to get a single formula, if possible, so I can avoid having hundreds of CF formats in the "CF rules box".

Thanks for any ideas. I'm sure it is a simple fix, I'm just overthinking.

2
excelconditional-formatting

2 Answers

4
votes

I think the formula you're looking for is:
=A2=MAX($A2:$L2)

enter image description here

This compares the value of the cell to the max value of the row. When equal, the formula evaluates to TRUE.

2
votes

Here's an answer to the question asked in the comments, i.e., what's going on with evaluating MAX to TRUE or FALSE in conditional formatting.

In Excel zero evaluates to FALSE and all other numbers to TRUE (or something like that). So for instance =NOT(NOT(0)) evaluates to FALSE.

When you entered your MAX formula in conditional formatting it will evaluate to TRUE unless the MAX formula result is zero, in which case it results in FALSE:

enter image description here