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:

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Employee Number[/TD]
[TD]Name[/TD]
[TD]Date of Birth[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]ABC[/TD]
[TD]01-Aug-1989[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]DEF[/TD]
[TD]05-May-1986[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]GHI[/TD]
[TD]06-Sep-1984[/TD]
[/TR]
[TR]
[TD]653[/TD]
[TD]JkL[/TD]
[TD]20-Mar-1987[/TD]
[/TR]
</TBODY>[/TABLE]


Old Sheet:
[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Empl #[/TD]
[TD]Status[/TD]
[TD]Employee Name[/TD]
[TD]Place[/TD]
[TD]DOB[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Permanent[/TD]
[TD]ABC. D[/TD]
[TD]N/A[/TD]
[TD]01-Aug-1989[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Temporary[/TD]
[TD]DEF[/TD]
[TD]N/A[/TD]
[TD]07-Jun-1983[/TD]
[/TR]
[TR]
[TD]78900[/TD]
[TD]Permanent[/TD]
[TD]GHI[/TD]
[TD]N/A[/TD]
[TD]06-Sep-1984[/TD]
[/TR]
[TR]
[TD]6534[/TD]
[TD]Part-Time[/TD]
[TD]JKLE[/TD]
[TD]N/A[/TD]
[TD]20-Mar-1987[/TD]
[/TR]
[TR]
[TD]548[/TD]
[TD]Permanent[/TD]
[TD]MNO[/TD]
[TD]N/A[/TD]
[TD]21-Jun-1989[/TD]
[/TR]
</TBODY>[/TABLE]
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