Excel 2007 : Conditional Formatting and Changing the Color of Cells

[gagandeep4687]

Hello ,

I have an excel sheet with Two Columns, - Column A and B.

Each Column has more than 200 rows, What I want to do is to compare data of both columns and then Color the Specific Cells using Conditional Formatting.

For Eg:- If A5 < B5 then A5 should Go Red. and same with all other cells of Column A , Please tell me how can i do this , As I can't do it manually for more than 200 rows.

Best Regards

Gagan

[NBVC]

Select Column A range and go to the Home Tab.

Click Conditional Formatting and choose New Rule

Select "Format only cells that contain"

And then Select Is Less than and enter =B1 (where B1 corresponds to first cell in column A that you selected)

Click Format and choose Red from the Fill tab.

[gagandeep4687]

Hello NBVC,

Thanks for Reply , But whether This would only consider value of B1 for comparing all Cells in Column A??

I want to do like this, Compare A1 with B1 , A2 with B2 , A3 with B3.

i was trying to put a rule for first row of both columns , then using Paste special to paste rules to other rows but it did not worked

Please give advice

Regards
Gagan

[teylyn]

gagandeep, before you doubt that it works, have you actually tried NBVC's suggestion? The way it is set up, each cell in column A will be compared with column B on the same row, because the cell reference used in the setup is relative, i.e.

=B1

It is important that you enter the cell reference without $ signs, so better type it in instead of clicking on cell B1.

hth

[gagandeep4687]

Yes, It worked , but its not formatting correctly , like some rows which should be filled with color are not being filled . file is in .csv if that helps

[teylyn]

Post a small sample and point out the cells that don't format correctly.

[gagandeep4687]

Hello Teylyn ,

the method worked, its formatting properly now , Thanks for the help ,

Best Regards

Gagan