2 Database tables comparission in Excel

[prasadsbrk]

HI,

I've the QA Data in Shee1 & Prod Data in Sheet 2 of a workbook. Prod has 9 records & QA data has 6 records (missing 2nd, 5th & 8th records). I need to compare both the sheets with corsponding records and highlight the data mismatches as well as records missing. Can any one help me on this.

[venkatbatchu]

Hi,
Try with the below code u would get expected result....
----------------------

Code:

Set objExcel = CreateObject(”Excel.Application”)
objExcel.Visible = True
Set Wk1= objExcel.Workbooks.Open("File path1")
Set Wk2= objExcel.Workbooks.Open("Filepath2")
Set Ws1= Wk1.Worksheets(1)
Set Ws2= Wk2.Worksheets(1)
   For Each cell In Ws1.UsedRange
       If cell.Value <> Ws2.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next
set objExcel=nothing

------------------------

[prasadsbrk]

Hi,

The same code i'm using & it is comaring Sheet1 Row1 with Sheet2 Row1, Sheet1 Row 2 with Sheet2 Row2, .. so on.
But for me Data is in random rows as below

Code:

Sheet1 Row1 = Sheet2 Row1
Sheet1 Row2 = Sheet2 Row2
Sheet1 Row3 = <<No data here>>
Sheet1 Row4 = <<No data here>>
Sheet1 Row5 = Sheet2 Row3
Sheet1 Row6 = Sheet2 Row4
<<No Data here>> = Sheet2 Row5

.............
..........
like so on
But common field is there to (Column A) to know, so using this i need to compare. as below

- If <<NO DATA>> highlight the entire row in one color (e.g. Sheet1 in Yellow & Sheet2 in Green)
- If DATA Found (with ref. to Column A) and mismatches in other columns highlight in one color (e.g Red)

Can any one help me on this.

Thanks