Excel 2003 Conditional formatting challenge question

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John

One way:

Select A1:C6, with A1 the active cell. Enter this CF:

Formula is = A1 = MAX(A1,A8)

Select A8:C13, with A8 the active cell. Enter this CF:

Formula is = A8 = MAX(A1,A8)
In article ,
John C. wrote:

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

Use this idea. formula is
=a1=max($a$1:$a$8)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C." wrote in message
...
I am familiar with how to find a min and max value in a table and change
the
color of the cell and even the font to bold. However, I am stumped with
this
problem. Let's say that I have data in a table from A1 to C6, and from A8
to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your
consideration.

John

why haven't you used solutions provided to your post of 05:15 in the
NG?

was there anything wrong with them?


On 9 Lis, 14:10, John C. wrote:
I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. *However, I am stumped with this
problem. *Let's say that I have data in a table from A1 to C6, and from A8 to
C13. *I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? *Thanks for your consideration.

John

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John

Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What if
I left it out? Also, once I establish the first and second table, do i then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John


"Luke M" wrote:

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John

Did you see my post
=a1=max($a$1:$a$8)
copy in range a1:a8
The absolutes restrict the area to look at

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John C." wrote in message
...
Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What
if
I left it out? Also, once I establish the first and second table, do i
then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John


"Luke M" wrote:

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and
change the
color of the cell and even the font to bold. However, I am stumped
with this
problem. Let's say that I have data in a table from A1 to C6, and from
A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight
the
maximum one for each comparison, so that I am comparing apples to
apples.

Will someone please explain how to do this? Thanks for your
consideration.

John

Luke's formula is for the "Formula is" option in CF, whereas if you were
using =MAX(A1,A8) that would be in the "Cell value is" option.

No, you don't need to copy, and paste special format, because Luke told you:
"Select the first table, A1:C6, with A1 being active cell" before you insert
your CF conditions, and that is what he meant you to do.

The $ signs in =$A1=MAX($A1,$A8) change the column references from being
relative references to being absolute references. Look these terms up in
Excel help. That means that the whole of your range from column A to column
C will be using column A references to determine the format. Select one of
the other cells (C6, for example) and you'll see (if you have used the $
signs in the formula) that they still have the $A column references, though
the row references (without a preceding $ sign) are incremented from 1 to 6
()and from 8 to 13). By contrast if you don't include the $ signs, both row
and column references are incremented as you change row or column.
--
David Biddulph


"John C." wrote in message
...
Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What
if
I left it out? Also, once I establish the first and second table, do i
then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John


"Luke M" wrote:

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and
change the
color of the cell and even the font to bold. However, I am stumped
with this
problem. Let's say that I have data in a table from A1 to C6, and from
A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight
the
maximum one for each comparison, so that I am comparing apples to
apples.

Will someone please explain how to do this? Thanks for your
consideration.

John

Using MAX() will return true if both cell values are same or blank...Try the
below

To your questions
--CF expects either a true or false.=A1A8 returns either a TRUE or FALSE
--To your question.CF applies for the entire selection.So need to copy paste.

1. Select the cell/Range (say A1:C6). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=A1A8
4. Click Format ButtonPattern and select your color
5. Hit OK

6. Now select the cell/Range (say A8:C13). Please note that the cell
reference A8 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

7. From menu FormatConditional Formatting
8. For Condition1Select 'Formula Is' and enter the below formula
=A8A1
9. Click Format ButtonPattern and select your color
10. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"John C." wrote:

Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What if
I left it out? Also, once I establish the first and second table, do i then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John


"Luke M" wrote:

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John

Typo....

CF applies and adjusts the formula for the entire selection.So *** NO ***
need to copy paste....

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Using MAX() will return true if both cell values are same or blank...Try the
below

To your questions
--CF expects either a true or false.=A1A8 returns either a TRUE or FALSE
--To your question.CF applies for the entire selection.So need to copy paste.

1. Select the cell/Range (say A1:C6). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=A1A8
4. Click Format ButtonPattern and select your color
5. Hit OK

6. Now select the cell/Range (say A8:C13). Please note that the cell
reference A8 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

7. From menu FormatConditional Formatting
8. For Condition1Select 'Formula Is' and enter the below formula
=A8A1
9. Click Format ButtonPattern and select your color
10. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"John C." wrote:

Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What if
I left it out? Also, once I establish the first and second table, do i then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John


"Luke M" wrote:

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John