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.
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
------------------------
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