Highlight highest lowest value every nth row in a column

**Cicada** · 07-22-2011, 07:48 PM

Hi all,

Another question one day hopefully i will be able to contribute. I know it is possible to highlight the highest and lowest value in a given range, however what if i wanted to highlight the highest and lowest every nth row. I need to highlight the highest and lowest value every five rows i.e if i have a range of A1:A100 i woud like to highlight the highest and lowest value in A1:A5, A6:A10 and so on. is this possible? Thanks in advance for your help.

Regards
Dan

**Ron Coderre** · 07-22-2011, 08:43 PM

Related to my other post from you about averaging,

EDITED TO INCLUDE THIS COMMENT:
I just noticed that you want to highlight the max and min...not calculate them.
Consequently, the below may be irrelevant.
------------------------------------

These regular formulas, when copied down, return the MIN value for each successive series of 5 cells
B1: =MIN(INDEX(A:A,(ROWS($1:1)-1)*5+1):INDEX(A:A,(ROWS($1:1))*5))
or
B1: =MIN(OFFSET($A$1:$A$5,(ROWS($1:1)-1)*5,))

and these regular formulas return the MAX
C1: =MAX(INDEX(A:A,(ROWS($1:1)-1)*5+1):INDEX(A:A,(ROWS($1:1))*5))
or
C1: =MAX(OFFSET($A$1:$A$5,(ROWS($1:1)-1)*5,))

Does that help?

**JBeaucaire** · 07-22-2011, 08:45 PM

1) Highlight your range of cells from A1: A100 (this is designed to work from row 1)

2) Open the Format > Conditional Formatting and apply the following settings:

Condition1: Formula Is: =OR(A1=LARGE(INDEX(A:A,FLOOR(ROW()-1,5)+1):INDEX(A:A,CEILING(ROW(),5)),1),A1=SMALL(INDEX(A:A,FLOOR(ROW()-1,5)+1):INDEX(A:A,CEILING(ROW(),5)),1))
Format... Pattern: Yellow

That will highlight the high and low of each 5 row group.

**tigertiger** · 07-22-2011, 09:36 PM

and an alternative

Conditional formatting
+ select the data
+ Format / Conditional Formatting
with
Conditional 1: Formular is =A1=MAX(OFFSET(A$1:A$5,5*INT((ROW(1:1)-0.5)/5),))
Conditional 2: Formular is =A1=MIN(OFFSET(A$1:A$5,5*INT((ROW(1:1)-0.5)/5),))

see the attached file for detail, such as an example for A1:A15

**kvsrinivasamurthy** · 07-25-2011, 05:08 AM

Pl run this macro in your work sheet.You will get desired result.To change the colour of highlights change colorindex value in the macro.

Please Login or Register  to view this content.

**Cicada** · 07-25-2011, 02:21 PM

Thank you all for your help much appreciated. wow so many replys i dont know where to start, Will let you know how i get along. Thanks Again

Regards
Dan

**JBeaucaire** · 07-25-2011, 04:25 PM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**Jinxie** · 02-04-2013, 12:53 PM

Hi everyone, I have an issue similar to this I have a spread sheet with several columns of data. I need to highlight and create a new column with the highest value in each row, using only column F, H, and J.

Does that make sense? I have used conditional formatting to get it to highlight the highest in one row at a time, but I can not figure out how to copy the formatting down to the other rows. I am not even sure if that is possible, or I need to write the formatting code differently.

Can someone offer a bit of help? Pretty please, and thank you with cherries on top

**arlu1201** · 02-04-2013, 02:22 PM

Jinxie,

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

New threads typically get faster response, too. Old threads are often only monitored by the original participants, greatly increasing response times.