Highlight exactly two largest cells

**robomb162** · 05-19-2011, 10:01 PM

Hello everyone,

I know how to use conditional formatting to highlight the two largest cells from a collection. For example, if my data is in cells A1 through A10 I could use

=OR(LARGE(A1:A10,1),LARGE(A1:A10,2).

What I would like to do is make sure that exactly 2 cells are highlighted. So if my data values are

10
6
6
4
3
...

I would like only the 10 and the first 6 to be highlighted.

Actually, my problem is a little more complicated. I have two categories of values. Let's call them categories A and B. I made a category C of the largest 4 values from category A. Then I made a category D of the largest 8 values from categories B and C. What I would like to do is highlight the 8 original cells that were used to make up category D. One approach I thought to solving this problem is to use my functions which create categories C and D to highlight the original cell instead of using the "conditional formatting" tool.

So maybe the better question is, "Is there a way to highlight a cell which is the reference for a function"? Sorry, I'm not sure of a better way to phrase that question.

Thanks!

**snb** · 05-20-2011, 04:49 AM

To post an example workbook most of the time is better to explain what you want.

**robomb162** · 06-02-2011, 03:18 PM

Of course you are correct, thanks. I've attached a sample workbook.

The three categories are "Singles Tournaments" (columns C-I), "Gender Doubles" (N-T), and "Mixed Doubles" (U-AA). The top four numbers in "Singles Tournaments" is taken and put into a new category (J-M). Then I take the top 8 numbers in columns J-AA and add them up.

In the example "Player A" that I give, you will notice entries E4 and H4 are not used in this sum, and only one of the entries S4 and X4 is used in the sum.

What I would like to do is have Excel automatically highlight the 8 original numbers, in this case C4, D4, F4, G4, N4, O4, U4, and one of S4 or X4, if possible. If there is no way around highlighting both S4 and X4, that is acceptable, but unfortunately it will mean a lot of highlated 0's for some rows, which is annoying.

This is going to be a user viewed document with columns J-M and AB-AI hidden.

Thanks for any help!

**robomb162** · 06-05-2011, 08:43 PM

Any ideas how to do this would be appreciated.

**robomb162** · 06-12-2011, 04:06 PM

Bump. Anybody?

**Haseeb Avarakkan** · 06-12-2011, 04:53 PM

Hello robomb162,

Try this formula in the conditional formatting,

Select the range C4:AA40 (make sure C4 is the active cell, start to select from C4)

=AND(N(C4),MATCH(C4,$AB4:$AI4,0),COUNTIF($C4:C4,C4)=1)

Choose the format.