Comparing two worksheets and highlight unmatched Data

[Vignesh Rocks]

Hi Team,

I have did a search for what i am looking for but I haven't got what i am exactly seeking. I am sure, here the experts can help me with this.

I have two worksheets named New and Old respectively and the datas would look like below.

New Sheet:

Employee Number	Name	Date of Birth
123	ABC	01-Aug-1989
456	DEF	05-May-1986
789	GHI	06-Sep-1984
653	JkL	20-Mar-1987

<TBODY>
</TBODY>

Old Sheet:

Empl #	Status	Employee Name	Place	DOB
123	Permanent	ABC. D	N/A	01-Aug-1989
456	Temporary	DEF	N/A	07-Jun-1983
78900	Permanent	GHI	N/A	06-Sep-1984
6534	Part-Time	JKLE	N/A	20-Mar-1987
548	Permanent	MNO	N/A	21-Jun-1989

<TBODY>
</TBODY>

In the above, New sheet needs to compare with old sheet and the columns that needs to refer are Employee Number, Name and DOB.

If employee # is not matching then mark the entire row in Red since Employee # can be the Key to search in old sheet.

The thing here is, if any data that is not matching for a particular employee, then that cell has to be highlighted in Red and the matching data in Green.

Here to highlight, i have used the Font color but it would be great if we can do this as a fill color.

Please advice if this can be done.

Your help will be really appreciated.

 

[alansidman]

Here is one solution

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Employee Number	Name	Date of Birth				Empl #	Status	Employee Name	Place	DOB
2	123	ABC	1-Aug-89	123ABC32721	123ABC32721	1	123	Permanent	ABC	N/A	1-Aug-89
3	456	DEF	5-May-86	456DEF31537	456DEF30474	#N/A	456	Temporary	DEF	N/A	7-Jun-83
4	789	GHI	6-Sep-84	789GHI30931	789GHI30931	3	789	Permanent	GHI	N/A	6-Sep-84
5	653	JkL	20-Mar-87	653JkL31856	6534JKLE31856	#N/A	6534	Part-Time	JKLE	N/A	20-Mar-87
6					548MNO32680	#N/A	548	Permanent	MNO	N/A	21-Jun-89
Sheet1

 

[Vignesh Rocks]

Hi Alansidman,

Thanks for your suggestion. I have tried the functions earlier and I would like to know if this is possible by automating it using VBA.



Thanks,
Vignesh