Change the color of cells in one column when they don t match cells in another column

Question

I want to check if the values in one column are the same as values in another column  Whenever the values are not the same  I would like to change the color of these cells   For example     Column I  Column AA both have the value of a the first month in years from 1318 till 1500  Arabic Calender  but I want to check which of these values doesn t match and color them with yellow for example   In this case  both cells in row 3 should have a different color after the checking operation   Is there a way to do this

User · Answer

In my case I had to compare column E and I.

I used conditional formatting with new rule. Formula was "=IF($E1<>$I1,1,0)" for highlights in orange and "=IF($E1=$I1,1,0)" to highlight in green.

Next problem is how many columns you want to highlight. If you open Conditional Formatting Rules Manager you can edit for each rule domain of applicability: Check "Applies to"

In my case I used "=$E:$E,$I:$I" for both rules so I highlight only two columns for differences - column I and column E.

User · Answer

Select your range from cell A  or the whole columns by first selecting column A   Make sure that the  lighter coloured  cell is A1 then go to conditional formatting  new rule    Put the following formula and the choice of your formatting  notice that the  lighter coloured  cell comes into play here  because it is being used in the formula      A1 lt  gt  B1    Then press OK and that should do it

User · Answer

you could try this   I have these two columns  column  A  and column  B    I want to color them when the values between cells in the same row mismatch   Follow these steps    Select the elements in column  A   excluding A1   Click on  Conditional formatting -  New Rule -  Use a formula to determine which cells to format   Insert the following formula   IF A2 lt  B2 1 0   Select the format options and click  OK   Select the elements in column  B   excluding B1  and repeat the steps from 2 to 4

[excel] Change the color of cells in one column when they don't match cells in another column

Examples related to excel

Examples related to conditional-formatting