# Conditional Formatting Help

Jay
Guest
Posts: n/a

 20th Feb 2007
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

MartinW
Guest
Posts: n/a

 20th Feb 2007
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

David McRitchie
Guest
Posts: n/a

 20th Feb 2007
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
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
>
>

Jay
Guest
Posts: n/a

 1st Mar 2007
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
> 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
>>
>>

>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Greegan Microsoft Excel Worksheet Functions 5 31st Oct 2005 03:19 PM =?Utf-8?B?QnJhaW5GYXJ0?= Microsoft Excel Worksheet Functions 1 13th Sep 2005 05:45 PM =?Utf-8?B?S1dHU05B?= Microsoft Excel Programming 2 16th Mar 2004 07:56 PM -\$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM John Meyer Microsoft Excel Discussion 8 21st Dec 2003 09:37 PM

Features