We have two sheets, Sheet1:

enter image description here

and Sheet2:

enter image description here

Both sheets have approximately the same amount of data, but there are differences. I want to match IDs between Sheet1 and Sheet2 and highlight any differences on Sheet1 found in the corresponding row, like so:

enter image description here

My attempt at the conditional formatting logic is as follows: Do a lookup on A2 of Sheet1 in the A column of Sheet2 and if there is a match, compare Sheet1's B2-E2 to the B-E match of Sheet2. Move to the A3 of Sheet1 and repeat.


The formula to use in the Conditional Formatting is:


This is for column B, and just change the B2 at the end to C2 etc. for the other columns, and for each column increase the 2(after the A1:E1000) with one number.

Here are screenshots of how if should be. tested and works for me: enter image description here

And here is the furmula for coulmn B and C: enter image description here

enter image description here

  • This method seems to be on the right track, but it highlighted the wrong fields – dzampino Feb 2 '17 at 15:00
  • Can you explain? Which fields did you want it to highlight and which fields did it highlight? – Yisroel Tech Feb 2 '17 at 15:14
  • Oh....that happens when you're not following exactly.... You applied it to the whole columns so it is everything with one cell up, you should've done the "applies to" just from row 2. Like "=$B$2:$B$1000" instead of "=$B:$B" – Yisroel Tech Feb 2 '17 at 19:23
  • I updated it, but with no luck – dzampino Feb 3 '17 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.