Conditional format top and bottom 2 values in each row

[lukela85]

Hi all,

I have a worksheet with around 1300 rows. I have a conditional format set on the first row to highlight the top 2 and bottom 2 values in cells B2:S2. I would like to be able to copy the conditional format for all rows in the worksheet. So in the next row down highlight top and bottom 2 in cells B3:S3 and so on. Is this possible?

Thanks,

Luke

[AliGW]

Yes - all you need to do is go to the dialog box for the rule(s) you have already set and change the Applies To field to the entire range, not just the row in question (e.g. instead of $B$2:$S$2, change it to $B$2:$S$1300). There should be no need to change anything else.

[lukela85]

That only highlights the top 2 and bottom 2 values in the entire worksheet, not the top 2 and bottom 2 in each individual row?

[lukela85]

Does anyone else have any ideas on this?

Thanks,

Luke

[AliGW]

Not unless you attach a sample workbook.

[lukela85]

Sorry I should have attached a sample workbook to my original post! I have attached a slimmed down version of what I'm after. Thanks

[FlameRetired]

• As Ali states: Extend the ranges in the "Applies To field" to $B$2:$E$1300 for both rules.
• For the SMALL 1,2 rule change the rule type to "Use a formula to determine which cells to format". Tab to "Format values where this formula is true:" and use this formula

  Please Login or Register  to view this content.

• Do the same for the LARGE 1,2 rule and use the formula

  Please Login or Register  to view this content.

Note the relative and absolute cell references. CF can be a little tricky and not so intuitive on that count.

[lukela85]

Thanks, works perfectly!

[FlameRetired]

You're welcome. Good to hear.

Glad to help. Thanks for the feedback, added rep and marking this thread Solved.