Thread: Highlighting Rows below a Maximum Value

Hello all,

I'm looking for assistance in the following problem, and would appreciate any and all suggestions/solutions. Thank you in advance!

Here is the situation:

1) I have dynamic data in two columns A & B, measuring 10 rows x 2 columns. (A1:A10 and B1:B10)

2) I have a column of whole numbers in A1:A10, and have found the maximum value using =MAX(A1:A10).

3) I would like to shade the two rows immediately below the maximum value: for example if the maximum was in cell A5, I would like A6:B6 and A7:B7 to be shaded.

Does anyone have suggestions on how to accomplish this task? I suspect conditional formatting and perhaps the OFFSET function might be used, but I'm really not too skilled in this area.

Much appreciated,
csaunders86

Another way to think of this problem is the following word algorithm:

IF (MAX of Column A = is above CURRENT cell by 1 OR 2 rows)

THEN, SHADE CURRENT cell.

ELSE DONT SHADE CURRENT cell.

Hello
You could try the following conditional formatting formula in cells A1:B10

=OR(ROW()=MATCH(MAX($A$1:$A$10),$A$1:$A$10,0)+1,ROW()=MATCH(MAX($A$1:$A$10),$A$1:$A$10,0)+2)

It seems to work for me.

Hope this helps

Thanks to DBY !! Exactly what I was hoping for - I didn't know about the MATCH() function. and daddylonglegs thanks too - you're right I will have to build in something that accommodates for duplicates, but I'm confident I can do that myself.
Cheers and I've added to your reputation's.
csaunders86

To forum admins...Please consider this thread SOLVED.