I have two rows where I need to show a yellow background if the top number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Try:

C6:
=ISNA(C6)
then:
=C6C7


C7:
=ISNA(C7)
then:
=C7C6
and then:
=OR(C7<C6,ISNA(C6))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"sweetsue516" wrote in message
...
I have two rows where I need to show a yellow background if the top number
is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Sandy,

Thank you answering my question.

Could you tell me what format should follow the =or(c7<c6, isna(c6))?

"sweetsue516" wrote:

I have two rows where I need to show a yellow background if the top number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Sorry I misread your conditions, will:

C6:
=ISNA(C6) with no formatting
=ISNA(C7) with yellow formatting
=C6C7 with yellow formatting


C7:
=OR(ISNA(C7),C7C6) with no formatting
=ISNA(C6) with yellow formatting
=C7<C6 with yellow formatting

do what you want?

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"sweetsue516" wrote in message
...
Sandy,

Thank you answering my question.

Could you tell me what format should follow the =or(c7<c6, isna(c6))?

"sweetsue516" wrote:

I have two rows where I need to show a yellow background if the top
number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains
the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Sandy,

The "N/A" is not a error message but is a value I put in the cell.
So, if the cell value is "N/A" in either C6 or C7 the format for both cells
should be no formatting.

Does this make a difference in what you would do for this situation?

The desired results are

If C6C7 yellow background in both
If C7C6 no formatting in either
If either C7 or C6 = "N/A" no formatting in either

Thank you in advance.

"sweetsue516" wrote:

I have two rows where I need to show a yellow background if the top number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Does this make a difference in what you would do for this situation?

Yes that simplifies it a lot.

Both C6 & C7:

=OR($C$6=""N/A",$C$7="N/A") with no format

=$C$6$C$7 with yellow formating

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"sweetsue516" wrote in message
...
Sandy,

The "N/A" is not a error message but is a value I put in the cell.
So, if the cell value is "N/A" in either C6 or C7 the format for both
cells
should be no formatting.

Does this make a difference in what you would do for this situation?

The desired results are

If C6C7 yellow background in both
If C7C6 no formatting in either
If either C7 or C6 = "N/A" no formatting in either

Thank you in advance.

"sweetsue516" wrote:

I have two rows where I need to show a yellow background if the top
number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains
the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

Sandy,

That's it!!!!

Thank you!

It made things a lot easier once I explained it correctly.


"sweetsue516" wrote:

I have two rows where I need to show a yellow background if the top number is
higher than the bottom.

I have done a conditional format which works great until it runs into the
occassional cell that contains a N/A because there is no data to report.
(The cell is suppose to contain the N/A)

How can I hightlight the top row and bottom row it the top row cotains the
hightest number and have neither of them highlight if the top row has the
lowest or a N/A.

Currently conditional format is.

Formatting in C6
if c6=N/A no formatting
if c6 is greater than c7 yellow background

Formatting in C7
if c7=N/A no formatting
if C7 is greater than c6 no formatting
if c7 is less than c6 yellow background (this is the problem area, if c6
equals N/A it is reading it as less than)

Any suggestions?

"sweetsue516" wrote in message
...
Sandy,

That's it!!!!

Thank you!

It made things a lot easier once I explained it correctly.

You're welcome. Just glad we got it worked out eventually :-)

--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk

?