Compare two columns (conditional formatting)

I'm trying to do a comparison of two entire columns (H & L) where I'm trying to find the data where H < L and then flag those cells as red.

I tried to do a conditional formatting and put it in like (=$H:$H < $L:$L), and choose to flag each of those cells as red. The issue that I receive is that it states that I cannot perform this task on a range, but only use one cell. Granted I could do this for 500 cells, but I'd rather not key in the formula for every cell.


I tried to just do it for one and copy and paste (special - formula only) to the other cells, and for some reason, it still takes the actual "text/number" from that cell and pastes it over all of the other cells, so now I have identical cells, that don't do a comparison...

I don't know if this can be performed with conditional formatting, or if it would be easier to write it as VBA and do it that way -- I am open to suggestions.

Thanks in advance.


Notes: These cells are numbers only with a header (in text), and I'm performing this on Office 2007.

When I do Paste Special (Formats) all of the other cells just get the formatting of the first cell, even if the rule doesn't apply to them.
What happened was I wrote the formula to be "=$H$2<$L$2". In this case H2 is less than (<) L2, so it changed it to "Light Red Fill with Dark Red Text".

When I copy that over to the other cells (Format) it takes that format (the red fill/text) and changes the other cells to that even if they are not less than the corresponding L (or L2 for that matter).

Any other thoughts?

I removed all conditional formatting and started over. Did as you mentioned as far selecting the range and then choosing L2. When I did that it flagged all of the cells in H that were less than L2...doing the reverse (as mentioned) it flags all of the cells in L that are less than H2. I'm not sure if that's what you were thinking would happen or not.

What I really need to know is:
H2<L2
H3<L3
...
H400<L400
etc.

I've attached a sample of the spreadsheet (with all personal data removed). The only thing remaining are the 'H' and 'L' columns.
Hopefully this helps.

Thanks again.

interesting....I'm not quite sure why this didn't work the first time (or more likely, what I screwed up); however, what you've (ChemistB) done seems to work perfectly.

Thank you very much. This will save a lot of headache in the future.

As far as I'm concerned this thread can be closed.

Originally Posted by ChemistB

I created this conditional formatting exactly as I described in my previous post. Here's the result.

ChemistB - thanks very much. Works like a charm :-)

Can someone help me with this , i got the concept , but the issue here is it isnt comparing with column it is comparing with first column itself.

Please help , attached file is here

http://www.mediafire.com/view/?jl9qu63laa65gol

Also please explain how do you do that.

Thanks in Advance

Hi, while on the topic of comparing two columns to conditionally format a true condition, i'm wondering if it is possible (elegantly) to compare if values in column A are "near" the values in column B, of the corresponding row. For example, if value in A1 ,B1 is 92 and 100 respectively, is it possible to say " draw a red box when A1 is in the range of +/- 10% of B1" - effectivly giving a true condition when A1 is within the range 90-110. Is it possible to work with values like => 0.9*B1, for instance? Thanks in advance. :-)

I had been pulling my hair out for an hour trying to do exactly this. Thanks for the tip.

@ChemistB

Thanks a ton for uploading the file. After pulling my hair out for sometime, because I was doing exactly as prescribed, I realized that my spreadsheet was exported from Access. This for some reason was preventing any conditional formatting to work. So I decided to copy & paste the values into a new worksheet and then it worked! So presto!

Your directions worked perfectly, just needed to start on a blank spreadsheet.

Originally Posted by ChemistB

I created this conditional formatting exactly as I described in my previous post. Here's the result.

Thank you for this post. I was having the same issue and even struggled a couple of times to get this to work. Then I realized that excel had added "" around the cell and once I removed them per your instructions it worked perfectly.

Thanks!!!

This was very helpful. Thank you.

Use a formula to do the conditional formatting...

1. Clear all conditional formatting.
2. Select both colums.
3. Conditional Formatting -> New rule
4. Use a formula to determine which cells to format
5. Type in the formula: =$L2<$H2. LOCK the column reference, not the row reference!!!)
6. Set desired format.