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?

Answer 1

1. 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:

   

2. 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<>$B1
   

   

3. Then press OK and that should do it.

   

Answer 2

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:

1. Select the elements in column "A" (excluding A1);

2. Click on "Conditional formatting -> New Rule -> Use a formula to determine which cells to format";

3. Insert the following formula: =IF(A2<>B2;1;0);

4. Select the format options and click "OK";

5. Select the elements in column "B" (excluding B1) and repeat the steps from 2 to 4.

Answer 3

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.