Matching data on onesheet with another

QuestionEdit

I need to bump a list of serial numbers on onesheet with a list of serial numbers from another. If both sheets have a matching serial number I want the cells of both to change colors so I can see which cells don't have matching serial numbers. Can I do that?

AnswerEdit

If you have2010 or later or you are willing to create a defined name you can do that.

conditional formatting allows you to format cells conditionally

the countif function allows you to see if one cell matches values in another location. (or the match function)

Assume you want to use a defined name to identify the second set of numbers. Select all those numbers and then go to the name box in the left hand side of the formula are and type in LIST2 and hit enter

now go to the first sheet and select the serial numbers and again, go to the name box and Enter LIST1 and hit enter

Now with the first set of serial numbers selected, assume they are in A1:A100 and you have those selected with A1 as the activecell in the selection. Go to Data=>Validation in Excel 2003 and earlier and change Cell value is to Formula is by selecting it from the dropdown. Then put in the formula

=Countif(LIST2,A1)

click the format button and select a color. Repeat this for the second set of numbers, but the formula would be

=Countif(LIST1,A1)

If you have2007 or alter, conditional formatting is found on the home tab in the center. Click on it and select "new rule" towards the bottom. then select "use a formula to determine which cells to format". Now put in the formula above and then select the format you want applied.

In case you are not familiar with named ranges/defined names,

in Excel 2003 and earlier you can work with defined names by using Insert=>Name=>Define in Excel 2007 and later, on the formula tab, you will find the name manager.

Post a followup if any of this is not clear or you need more help. If you need a sample workbook, contact me at