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?

You can use Conditional Formatting for this. – Tim Williams Mar 05 '14 at 05:55 — Tim Williams, Mar 05 '14 at 05:55
@TimWilliams How can I do this? – user3381987 Mar 05 '14 at 06:01 — user3381987, Mar 05 '14 at 06:01

Jerry · Answer 1 · 2017-12-12T06:10:25.283

63

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<>$B1
```
Then press OK and that should do it.

edited Dec 12 '17 at 06:10

answered Mar 05 '14 at 06:02

Jerry

70,495
13
100
144

3

If you have trouble with this, make sure you leave off the `$` for the direction that will be changing. E.g. If you have column data, make sure you have a `$` only on the column (`$A1`, not `$A$1`). – Jonathon Reinhart Oct 25 '18 at 20:42

score 5 · Answer 2 · edited Nov 25 '16 at 12:02

5

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

edited Nov 25 '16 at 12:02

TooTone

7,129
5
34
60

answered Jun 15 '15 at 10:49

Federico

53
1
2

this is helpful but the `;` in the formula can be replaced with `,`. Also the `IF` is unnecessary: see the accepted answer. – TooTone Nov 25 '16 at 12:03
This is the correct answer for Office 2016 - you need the "if" formula. – Russ Aug 25 '23 at 12:39

score 4 · Answer 3 · answered May 23 '14 at 06:42

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.

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

3 Answers3

Linked