Could anyone please advise how I can conditionally format cells so that the
highest & lowest value in the range is formatted.

I've tried 'Formula is MAX($A$1:$A$10)' but no joy.

Any help greatly appreciated.

Thanks,


Jay

Hi Jay,

Try this, Select cells A1 to A10 go to CF
and set 'cell value is' 'equal to' and put your formula in the third box
=MAX($A$1:$A$10)

HTH
Martin

Hi Jay,
See my page
http://www.mvps.org/dmcritchie/excel/condfmt.htm

The selection has nothing to do with the range you are
picking the max from. The selection indicates the cells
which are to be possibly formatted. See the comments
near the top of the above web page written in RED.

Often you would select entire columns:
Select Column A if you only want cells in Column A formatted.
Select all cells, if you want entire rows formatted.

In your case you have a restricted range for the maximum so
you world probably only be formatting the same range (cols or rows)
Select A1:A10 if you only want to format cells in Column A
Select 1:10 if you want to format entire row on a column A max hit.

In you conditional formatting the formula is based on a comparison
of the active cell (or the offset from the active cell to cells referenced
in your formula). So if you selected A1:A10 *and* A1 is the
active cell.
Conditional Formula 1: =A1=MAX($A$1:$A$10)
But you can use the following instead which allows you to format
the entire row if you selected rows 1:110
Conditional Formula 1: =$A1=MAX($A$1:$A$10)

Every cell that was in the selection when you entered C.F. is tested
with the formula adjusted
A1: =A1=MAX($A$1:$A$10)
A2: =A2=MAX($A$1:$A$10)
A3: =A3=MAX($A$1:$A$10)
...
A10: =A10=MAX($A$1:$A$10)

If you wanted the entire row formatted, then you would have
selected rows 1:10 when you entered C.F., and the formulas would test
all cells in Row 1 test for: =$A1=MAX($A$1:$A10)
all cells in Row 2 test for: =$A2=MAX($a$1:$A10)
all cells in Row 3 test for: =$A3=MAX($A$1:$A10)
....
all cells in Row 10 test for: =$A10=MAX($A$1:$A10)

If you wanted to highlight each new maximum you could use
conditional formatting of so that the upper cell range of the
MAX function varies as the row changes.
=$A1=MAX($A$1:$A1)

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jay" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Could anyone please advise how I can conditionally format cells so that the
> highest & lowest value in the range is formatted.
>
> I've tried 'Formula is MAX($A$1:$A$10)' but no joy.
>
> Any help greatly appreciated.
>
> Thanks,
>
>
> Jay
>
>

Hi Dave,

Thanks a lot for the comprehensive response - it's really helped clarify my
understanding. And I've just printed copies of your page and distributed
to my staff as it's an excellent learning resource.

thanks again,

Jay


"David McRitchie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Jay,
> See my page
> http://www.mvps.org/dmcritchie/excel/condfmt.htm
>
> The selection has nothing to do with the range you are
> picking the max from. The selection indicates the cells
> which are to be possibly formatted. See the comments
> near the top of the above web page written in RED.
>
> Often you would select entire columns:
> Select Column A if you only want cells in Column A formatted.
> Select all cells, if you want entire rows formatted.
>
> In your case you have a restricted range for the maximum so
> you world probably only be formatting the same range (cols or rows)
> Select A1:A10 if you only want to format cells in Column A
> Select 1:10 if you want to format entire row on a column A max
> hit.
>
> In you conditional formatting the formula is based on a comparison
> of the active cell (or the offset from the active cell to cells referenced
> in your formula). So if you selected A1:A10 *and* A1 is the
> active cell.
> Conditional Formula 1: =A1=MAX($A$1:$A$10)
> But you can use the following instead which allows you to format
> the entire row if you selected rows 1:110
> Conditional Formula 1: =$A1=MAX($A$1:$A$10)
>
> Every cell that was in the selection when you entered C.F. is tested
> with the formula adjusted
> A1: =A1=MAX($A$1:$A$10)
> A2: =A2=MAX($A$1:$A$10)
> A3: =A3=MAX($A$1:$A$10)
> ...
> A10: =A10=MAX($A$1:$A$10)
>
> If you wanted the entire row formatted, then you would have
> selected rows 1:10 when you entered C.F., and the formulas would test
> all cells in Row 1 test for: =$A1=MAX($A$1:$A10)
> all cells in Row 2 test for: =$A2=MAX($a$1:$A10)
> all cells in Row 3 test for: =$A3=MAX($A$1:$A10)
> ...
> all cells in Row 10 test for: =$A10=MAX($A$1:$A10)
>
> If you wanted to highlight each new maximum you could use
> conditional formatting of so that the upper cell range of the
> MAX function varies as the row changes.
> =$A1=MAX($A$1:$A1)
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Could anyone please advise how I can conditionally format cells so that
>> the
>> highest & lowest value in the range is formatted.
>>
>> I've tried 'Formula is MAX($A$1:$A$10)' but no joy.
>>
>> Any help greatly appreciated.
>>
>> Thanks,
>>
>>
>> Jay
>>
>>
>
>