MrExcel Publishing
Your One Stop for Excel Tips & Solutions

re: compare 2 cells and change color of 1 if > than other


Posted by MikeK on November 06, 2001 7:29 AM

:Good Morning,
:I have 2 rows of data consisting of 17 numbers, and I would like to do a comparison on row 1, column A with row 2, column A only etc...
:Now if one of these cells is greater in value than the other, is there a way for this cell to turn 'blue', and the other cell 'green'?
:And can this be accomplished without conditional formatting every single column?
:I appreciate any assistance in this matter.


Posted by Bob Umlas on November 06, 2001 7:44 AM


use formula is with 2 conditions. First is:
=A1=MAX(A$1:A$2) and set pattern to blue;
second is
=A1=MIN(A$1:A$2) and set pattern to green

Posted by Mike K on November 06, 2001 7:57 AM

Bob,
thanks for your quick reply.
I tried it and it only partially works, it doesn't change cell A2, only A1

Posted by Troy Buzzard on November 06, 2001 8:14 AM

It could be done with a macro but that is beyond me. It would probably take longer to write the macro than it would to do the conditinal formating.
If you want to avoid doing a lot of manual conditional formating, there is a way to do it. Just conditionally format two cells properly and then use "Paste Special". Here is an example of how to do it.
1. Assuming rows 3 and 4 in your spreadsheet are blank, conditionally format cell A4 with this exact text (don't enter quotes): "Cell value is" "Greater than" "=A3".
2. Set the conditional format to a blue background.
3. Click the OK button to close the conditional formatting window.
4. Now copy cell A4 and past across B4 thru Q4.
5. Highlight A4 thru Q4 and then press <Ctrl><V> to copy these cells to memory.
6. Highlight A2 thru Q2.
7. Select "Edit","Paste Special".
8. Select "Formats" then click OK.
Now, any cell in row 2 will turn blue if the value is greater than that of row 1 and you have not lost your original data. Granted, you will have to enter two conditions instead of just the one used in this example but I think you can figure that one out.
Good luck.

Posted by Troy Buzzard on November 06, 2001 8:16 AM

It could be done with a macro but that is beyond me. It would probably take longer to write the macro than it would to do the conditinal formating.
If you want to avoid doing a lot of manual conditional formating, there is a way to do it. Just conditionally format two cells properly and then use "Paste Special". Here is an example of how to do it.
1. Assuming rows 3 and 4 in your spreadsheet are blank, conditionally format cell A4 with this exact text (don't enter quotes): "Cell value is" "Greater than" "=A3".
2. Set the conditional format to a blue background.
3. Click the OK button to close the conditional formatting window.
4. Now copy cell A4 and paste across B4 thru Q4.
5. Highlight A4 thru Q4 and then press (Ctrl)+(V) to copy these cells to memory.
6. Highlight A2 thru Q2.
7. Select "Edit","Paste Special".
8. Select "Formats" then click OK.
Now, any cell in row 2 will turn blue if the value is greater than that of row 1 and you have not lost your original data. Granted, you will have to enter two conditions instead of just the one used in this example but I think you can figure that one out.
Good luck.

Posted by Dan on November 06, 2001 10:20 AM

This may not be what you want but:

Highlight the first row (A), or the range of cells in the first row, and use Conditional format.
Use Formula Is and
First condition:
=A1>A2 and format blue
2nd Condition:
=A1<A2 and format green

Do the same for the 2nd row except use:
First condition:
=A2>A1 format blue
2d Condition:
=A2<A1 format green

That should do it for you. You didn't mention anything if the cells are equal in value, so I didn't include.
HTH

Posted by Dan on November 06, 2001 10:25 AM

Correction

Correction: my post didn't format correctly hopefully this time it will. See below...

Posted by Dan on November 06, 2001 10:34 AM

What's going on??? Sorry!! Re: Correction

My post still didn't format correctly!!!

1st Row -
1st condition -
=A1>A2
Format blue
2nd condition -
=A1<A2<br>Format green

2nd Row -
1st Condition -
=A2>A1
Format Blue
2nd Condition -
=A2<A1<br>Format Green.

I hope this one looks right in the post. If not go down to the "Comments" area where you post replies and look at the text in there. Sorry.

Posted by Dan on November 06, 2001 10:40 AM

I give up! I'll email you my response! Re: Correction

Posted by Juan Pablo on November 06, 2001 10:58 AM

Re: What's going on??? Sorry!! Re: Correction

Just put a space in between the " < ", like this:

= A1 < A2

Juan Pablo

Posted by IML on November 06, 2001 12:35 PM

Re: I give up! I'll email you my response! Re: Correction

Dan,
this board doesn't like when you paste > or < followed immediately by another letter. If you add a space before or after these, you may have better luck.

Posted by Dan on November 07, 2001 4:53 AM

Thanks Juan

Posted by Dan on November 07, 2001 5:05 AM

Thanks IML