Changing fill colour of cells in a range

[Paul Hyett]

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?

 

[Guest]

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

 

[Guest]

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]

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

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]

In microsoft.public.excel.misc on Wed, 11 Jul 2007, Paul Hyett

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

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?

 

[Gord Dibben]

Make that 3 CF per cell, not worksheet.


Gord Dibben MS Excel MVP

 

[Paul Hyett]

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

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]

In microsoft.public.excel.misc on Sat, 14 Jul 2007, Paul Hyett

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


Thanks - either that is different for the latest versions of Excel, or
I misinterpreted something previously.

Worked like a charm - I just had to change the absolute row reference to
a relative one, then copy it down all the rows I needed!

 

[Gord Dibben]

Excellent news.

Thanks for the feedback

In microsoft.public.excel.misc on Sat, 14 Jul 2007, Paul Hyett


Worked like a charm - I just had to change the absolute row reference to
a relative one, then copy it down all the rows I needed!