comparing two excel files

Hi all,
Im a novice in VB Script. I need some help.
 
The scenario is, i have 2 excel files. And both will/should have same number of worksheets.
1st worksheet of  file A should be matched with 1st worksheet of  file B in such a way that,
  The cell A1 should be checked with all the cells of 1st column in file B
    --> if A1's value is not found in the 1st column of file B, then it should be highlighted.
 
Example:
File A
Name    Empcode
John       3333
david      1111
 
 
file B
Name    Empcode
David      1111
 
Here as u can see david is in 2nd cell of File A's 1st column, whereas David is present in 1st cell of file B's column A. Hence cell to cell comparision (which is found in web) is not suitable.
 
 
Well, i surfed much and got some codes and i altered it according to my need. And i have highlighted the portion where im stuck (in different colors)
 

 dim path1,path2, strCount, objDialog, intResult Set objDialog = Createobject("Useraccounts.Commondialog")
    objDialog.Filter = "All files|*.*"
    objDialog.Filterindex = 1
MsgBox "select first input file.. "
    intResult = objDialog.Showopen
    IF(intResult = 0) THEN
      Wscript.quit
    ELSE
 path1= objDialog.FileName
MsgBox "select second input file.. "
    intResult = objDialog.Showopen
    IF(intResult = 0) THEN
      Wscript.quit
    ELSE
      path2= objDialog.FileName 
    END IF
End If dim objWorkbook1, objWorkbook2, objWorksheet1, objWorksheet2, i,c_1, c_2 Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(path1)
Set objWorkbook2= objExcel.Workbooks.Open(path2) strCount = objworkbook1.Worksheets.Count 
for i = 1 to strCount  Set objWorksheet1= objWorkbook1.Worksheets(i) Set objWorksheet2= objWorkbook2.Worksheets(i)    ' im stuck here
 For Each c_1 In objWorksheet1.UsedRange
         If c_1 <> objworksheet2.cell.value Then               c_1.Interior.ColorIndex = 3 'Highlights in red color if any changes in cells
       Else
           c_1.Interior.ColorIndex = 0        End If
   Next
Next set objExcel=nothing   

 
Please help me friends.

manosaran3
The scenario is, i have 2 excel files. And both will/should have same number of worksheets.
1st worksheet of  file A should be matched with 1st worksheet of  file B in such a way that,
The cell A1 should be checked with all the cells of 1st column in file B
--> if A1's value is not found in the 1st column of file B, then it should be highlighted.

Wouldn't it be easier to add a (hidden) column to the sheets in workbook A with a formula "=COUNTIF([File_B.xls]Sheet1!A:A;A2)", and a conditional format to highlight the cells if the value in the hidden column is 0?

I agree with u that using formulas we can achieve it. But actually. Im working in automating a big process.
 
Exporting result data from teradata and converting the text files into excel files and then comparing a source file with the result excel file.
 
Well i have the codes for text to excel conversion and so im stuck in excel comparison. Hence, help me.

Just a quick note, because it's late and I had a long day.

manosaran3

For Each c_1 In objWorksheet1.UsedRange
If c_1 <> objworksheet2.cell.value Then
 c_1.Interior.ColorIndex = 3 'Highlights in red color if any changes in cells
Else
 c_1.Interior.ColorIndex = 0
End If

This should give you some pointers:

For Each c_1 In objWorksheet1.UsedRange
 If c_1.Offset.Value <> "" Then WScript.Echo c_1.Offset.Row & "," & c_1.Offset.Column
Next