How to: Highlight lowest and highest values multiple times in same row?

Hi All

My query is, I wish to highlight the highest and lowest number in a row several times, so highlight the highest number green and lowest number red in cells I7 to I12, then apply the same rule again looking only in rows I15 to I20 and so on...

I can find the highest and lowest by using MIN etc, but not sure how to do this several times in the same row?

Thanks for any help.

Hi Luz1978,
Welcome to the Forum.

Without knowing your sheet layout and columns & rows of interest, suggesting accurate solution to your question is difficult.
So better you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Try this.....

Select the range H4:H30 and make a New Rule for conditional formatting using the formulas given below and set the format as per your choice.

For Green

Please Login or Register  to view this content.

For Red

Please Login or Register  to view this content.

For detail see the attached sheet.

Originally Posted by Luz1978

Apologies if this is not an easy question to ask.

Of course this is not an easy question to ask, because your sheet layout is unknown to me. That is why you were supposed to upload a sample workbook which has the sheet layout as same as your original sheet layout. When you upload a sample workbook which does not have the sheet layout as same as your original workbook, that means you are confident enough to make required changes on your own to meet the requirement of your original workbook.

In the formula suggested, E$4:E4 would be the first cell where you have inputted Overall in the col. E followed by Criteria 1, Criteria 2 etc with blank rows and in the offset function 3 refers to the column H because your reference cell is E4 so in order to offset to column H you need to add 3 (E->0,F->1,G->2,H->3). Therefore if your values are in column J instead of column H, you will need to add 5 (E->0,F->1,G->2,H->3,I->4,J->5) in the offset formula.
The 6 in the offset formula will be the height of the data in column H for one criteria. You have only 6 values for all the zones in each criteria. So if in case you have values for only 4 zones (East, West, North, South) for each criteria, you should use 4 instead of 6 in the formula.

Hope that with the help of the description above, you would be able to make the changes in the formula as per your need.