This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 93%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESH%3C/text%3E%3C/svg%3E)
I need to find a formula that supports the ability to highlight differences between two worksheets. Each worksheet has the same two columns. Those two columns are dependent of each other in order for the information to be correct. I need the formula to take into account Column A and Column B must match on both worksheets. If they do not match, then highlight the row that does not match.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
On sheet1, select the range of cells in column A to be checked. For example, A9:A22. On the Home tab, select Conditional Formatting -> New Rule -> Use a formula. Enter the formula "=sheet1!a9<>sheet2!a9" without the quotes. Click OK several times and force a mismatch in the data to test.
Repeat for column B
It has two dependent columns though! Like column a is states and column b is cities. Some states might have cities with similar names. So when highlighting differences between the worksheets both columns need to be taken into account. Additionally, they are not in the same order on each worksheet so row 9 on worksheet 1 might not match row 9 on worksheet 2 but it might be in another row.
You can expand the test two check multiple columns changing the formula to "(OR(column-1 test,column-2 test,...))". This will be true if any column miscompares.
I don't know of any way in conditional formatting the check row 1 against row 1 but row 2 against row 5.
In a macro, you could loop through each relevant row in sheet1. For the relevant cell(s) in that row, you check all the corresponding cell(s) in sheet2. If you find a match, you stop. If you finish the inner loop, there was no match and you highlight. You then repeat for the next row in sheet1. This will highlight the cells in sheet1 with no match in sheet2. A similar process will let you highlight the cells in sheet 2