Solved compare two columns in excel and colour cells

December 19, 2009 at 20:45:51
Specs: ms office , cpu
Hi,
I have two columns ,In ms excel 2003 ,suppose day to day rates of stocks,i want to compare two colums and if value in 2nd column is greater then cell must turn green or if value in 2nd column is less than 1st column cell must turn red.
Please help guys!!

See More: compare two columns in excel and colour cells

Report •


#1
December 20, 2009 at 06:07:01
✔ Best Answer
Hi,

Use conditional formatting.

If cell B1 is the cell to change color and cell B1 is compared to cell A1 then do the following:
Select B1
From the Menu bar - Format - Conditional formatting ...
In the dialog box that opens select 'Is Formula' in the Condition 1 drop-down
Enter this in the Formula box:

=IF(B1>A1,TRUE,FALSE)
Then click Format and select a Green color in the Pattern Tab.
Click OK.
In the conditional formatting box click Add>>
Select 'Is Formula' under Condition 2 and enter this in the Formula box:
=IF(B1=A1,TRUE,FALSE)
Then click Format and select a Yellow color in the Pattern Tab.
Click OK.
In the conditional formatting box click Add>>
Select 'Is Formula' under Condition 3 and enter this in the Formula box:
=IF(B1<A1,TRUE,FALSE)
Then click Format and select a Red color in the Pattern Tab.
Click OK.
Click OK in the conditional formatting box.

Cell B1 will now change color depending on the values in A1 and B1.

When cell B1 is dragged down, or copied and pasted, the conditional formatting will be copied as well.

If you wanted B1 to have no special color (just the normal cell background) if either A1 or B1 was empty, use the following three formulas:

=IF(OR(A1="",B1=""),FALSE,IF(B1>A1,TRUE,FALSE))

=IF(OR(A1="",B1=""),FALSE,IF(B1=A1,TRUE,FALSE))

=IF(OR(A1="",B1=""),FALSE,IF(B1<A1,TRUE,FALSE))

The basic concept with conditional formatting is that the format is used if the formula returns TRUE
If the formula returns FALSE, the format is not used, and the next formula is tested.
If all three formulas return FALSE, none of the formats is applied.

(In Excel 2007, the rules are slightly different also Excel 2007 allows many conditional formats per cell, Excel 2003 only allows three.)

Regards


Report •

#2
December 21, 2009 at 09:13:01
thank u sir,
i want to ask, if a column is added daily ,
like if column A represents name.
column B represents value on date 1
column C represents value on date 2
column D represents value on date 3
and like wise.......
any formula or array can be used on it ??
awaiting eagrly for your response.


Report •

#3
December 21, 2009 at 09:20:50
Latest column should be compared with prev column and provide o/p in color format of cells.

Report •

Related Solutions

#4
December 21, 2009 at 11:17:25
Hi,

Just drag the cells in column B to the right - through columns C, D etc.
Select them all and hit delete to remove any values in them.

Adding values to cells in column C will compare them to column B, and give you the color code, and cells in column D will be compared with cells in Column C and so on.

If you use my second formulas in column B

=IF(OR(A1="",B1=""),FALSE,IF(B1>A1,TRUE,FALSE))
=IF(OR(A1="",B1=""),FALSE,IF(B1=A1,TRUE,FALSE))
=IF(OR(A1="",B1=""),FALSE,IF(B1<A1,TRUE,FALSE))
, you should find that unused columns just have the background color until you enter data in them.

Conditional formatting formulas work just like ordinary formulas and change as you drag the cells. If you want a column for names (column A), right-click the column selector (A at the top) and Insert. Enter names in column A. Column C will now be compared to column B.

Regards


Report •


Ask Question