If I have a row of numbers, and I want the highest (or lowest) value
cell in that range to stand out using a different fill colour, does
anyone know of a way of doing this automatically?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Paul,

Use conditional formatting.

Select your range of cells then:-

Format|conditional formatting|cell value is|equal to

enter the formula

=Max($A$1:$A$5)
Pick a colour

Click add and repeat this but this time use Min instead of Max in the formula.

Mike

"Paul Hyett" wrote:

If I have a row of numbers, and I want the highest (or lowest) value
cell in that range to stand out using a different fill colour, does
anyone know of a way of doing this automatically?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Wed, 11 Jul 2007, Mike H
wrote :
Paul,

Use conditional formatting.

Select your range of cells then:-

Format|conditional formatting|cell value is|equal to

enter the formula

=Max($A$1:$A$5)
Pick a colour

Click add and repeat this but this time use Min instead of Max in the formula.

OK - this does work, but I have several hundred rows of figures*, and
IIRC you can only have around 3 conditional formats on one spreadsheet?

*One row for each day of the year, with 20+ years of daily temperature
readings across it - I want to highlight which year had the
hottest/coldest reading for each date.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

In microsoft.public.excel.misc on Wed, 11 Jul 2007, Paul Hyett
wrote :
In microsoft.public.excel.misc on Wed, 11 Jul 2007, Mike H
wrote :
Paul,

Use conditional formatting.

Select your range of cells then:-

Format|conditional formatting|cell value is|equal to

enter the formula

=Max($A$1:$A$5)
Pick a colour

Click add and repeat this but this time use Min instead of Max in the formula.

OK - this does work, but I have several hundred rows of figures*, and
IIRC you can only have around 3 conditional formats on one spreadsheet?

*One row for each day of the year, with 20+ years of daily temperature
readings across it - I want to highlight which year had the
hottest/coldest reading for each date.

I'm guessing this can't be done, then?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Make that 3 CF per cell, not worksheet.


Gord Dibben MS Excel MVP

On Fri, 13 Jul 2007 17:35:02 GMT, Paul Hyett
wrote:

OK - this does work, but I have several hundred rows of figures*, and
IIRC you can only have around 3 conditional formats on one spreadsheet?

In microsoft.public.excel.misc on Fri, 13 Jul 2007, Gord Dibben
wrote :

On Fri, 13 Jul 2007 17:35:02 GMT, Paul Hyett
wrote:

OK - this does work, but I have several hundred rows of figures*, and
IIRC you can only have around 3 conditional formats on one spreadsheet?

Make that 3 CF per cell, not worksheet.

Gord Dibben MS Excel MVP

Thanks - either that is different for the latest versions of Excel, or I
misinterpreted something previously.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

format=Conditional Formatting

Select column

first CF:

Formula Is: =MIN(A:A)=A1

Set colour (pattern)


Second CF:

Formula Is: =MAX(A:A)=A1

Set colour

OK

HTH


"Paul Hyett" wrote:

If I have a row of numbers, and I want the highest (or lowest) value
cell in that range to stand out using a different fill colour, does
anyone know of a way of doing this automatically?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)