Excel: Highlighting the highest and lowest values

Figment

Hi

I need some help using conditional formatting please (or another way to automatically highlight the highest and lowest values in a number of cells within a row.)

For example, if I have the cell values:
C1=1, C3=3, C5=5, C7=7
D1=2, D3=1, D5=11, D7=2
E1=2, E3=2, E5=4, E7=2

I need C1, D3 and E1, E3, E7 to be highlighted in green, and C7, D5 and E5 highlighted in red.

Note: Columns 2,4,6,8 contain related data but do not need to be highlighted or included in any comparison.

Can this be done?

TIA

bengalknights

In conditional formatting do custom rule and set highlight to highest and lowest value in column

Figment

bengalknights wrote: »

In conditional formatting do custom rule and set highlight to highest and lowest value in column

Thanks, but I can't see these options within conditional formatting (Excel 2003). I don't think this would help anyway as the figures to compare are in alternate cells within a row.

bengalknights

Figment wrote: »

Thanks, but I can't see these options within conditional formatting (Excel 2003). I don't think this would help anyway as the figures to compare are in alternate cells within a row.

Ah ok just realised your on excel 2003. in 2007 onwards the option exists to pick it up on entire row, column or custom range

cookie365

http://www.mrexcel.com/forum/excel-questions/30625-highlight-lowest-value-row-via-conditional-formating.html

Figment

cookie365 wrote: »

http://www.mrexcel.com/forum/excel-questions/30625-highlight-lowest-value-row-via-conditional-formating.html

Thank you, that works except that it's treating blank cells as the minimum value. Is it possible to only apply conditional formatting on non-blank cells

EDIT: Discovered that adding the conditional format Formula Is =ISBLANK(ref) with a format of NO FILL ensures that only non-blank cells are highlighted.