Comparison of excel files, cell by cell below is the code that i created as a POC towards an implementation for 2 excel file comparison (cell by cell)

This was just to test the logic, which works as expected, however i found out that for big data (hundreds of rows and columns) this approach using excel object is very time consuming. Approx time taken for comparing 130 rows and 248 columns was about 10 mins.

Is there any way that i can optimize this

Note: " the code is at basic level" kindly answer towards optimization only

The Code is as below

Dim a()
Dim b()
x = 0
file1 = InputBox("Enter the first file")
file2 = InputBox("enter the second file")

'create FSO and check if the file exist
Set fso = CreateObject("Scripting.FileSystemObject")
 if (fso.fileExists(file1)) and (fso.fileExists(file2)) then
  MsgBox "The file exists"
Else
  MsgBox "The file doesn't exists"
  ErrCount = 1
End if

if Errcount &lt;&gt; 1 then 
 'Create excel object for both the files and compare
  Set objExcel = CreateObject("Excel.Application")
 'Iterate from worksheets for both the file
  Set objFile1 = objExcel.Workbooks.Open(file1)
  objFile1Count = objFile1.Worksheets.count

  Set objFile2 = objExcel.Workbooks.Open(file2)
  objFile2Count = objFile2.Worksheets.count
 End if

'sheet should be same
 if objFile1Count &lt;&gt; objFile2Count then
   ErrCount = 1
 End if
' Loop through the sheets
  if ErrCount &lt;&gt; 1 then
      For cnt = 1 to objFile1.Sheets.count
        Set file1Sheet = objFile1.Sheets(cnt)
        Set file2Sheet = objFile2.Sheets(cnt)
        'Check the sheet name
        if file1Sheet.Name &lt;&gt; file2Sheet.Name then
          ErrCount = 1
          Exit For
        Else
          'File 1
          file1RC = file1Sheet.usedrange.rows.count
          file1CC = file1Sheet.usedrange.columns.count
          'File 2
          file2RC = file2Sheet.usedrange.columns.count
          file2CC = file2Sheet.usedrange.columns.count

          'Iterate file
            For i = 2 to file1RC
              For j = 1 to file1CC
                      ReDim Preserve a(x)
                      ReDim Preserve b(x)
                      a(x) = Trim(file1Sheet.Rows(i).columns(j).value)
                      b(x) = Trim(file2Sheet.Rows(i).columns(j).value)
                      file2getRC = i &amp;"," &amp;j 
                      file1getRC = i &amp;"," &amp;j

                      'Check compare and add color
                       File1Val = a(x)
                       File2Val = b(x)

                       if File1Val = File2Val then
                          'do nothing as the values matches
                       Else
                          RNC = split(file1getRC, ",")
                          Col = RNC(1)
                          Row = RNC(0)
                          Col = CInt(Col)

                          ty = file1Sheet.Rows(Row).Cells(1, Col).value
                          file1Sheet.Rows(Row).Cells(1, Col).Interior.ColorIndex = 6
                          file2Sheet.Rows(Row).Cells(1, Col).Interior.ColorIndex = 6
                          objFile1.Save
                          objFile2.Save
                       End if 
                      x=x+1
              Next
            Next
           End if
        Next
  MsgBox "it works"
Else
  MsgBox "No file added or cancelled" 
    End if
You only ask about performance, but I want to point out anyway that there is a small error in the definition of file2RC: you used column instead of row. 

Now, let me get to the performance part. The thing making your code slow is that accesses to worksheets are expensive. Accordingly, they should be reduced to a minimum. 

Currently, you access each cell individually. A better approach is to load all on the same sheet in one go. You can do this by assigning the Value property, or better the Value2 property (Value causes problems with fields formatted as dates.), of the used range to a Variant variable. After that, the variable contains a two-dimensional array consisting of the values in the range. 

By comparing the values in corresponding arrays, you only need to access each sheet once to load the data and then for each time you find an error, in order to change the color.