Tom Black
asked on
Highlight Difference between two Sheets in the same workbook?
I have an interesting problem I think. I need to develop a VBA code to compare two Excel worksheets to find the differences, if any, for each store number listed on the current tab. As you can see from the abbreviated sample attached they are in random order and not all store numbers are listed on both sheets. This would be a simple manual process if not comparing 1500 lines and 85 columns of information.
I also need to highlight the entire line of data if a store number is not found on last weeks report tab.
Highlight-Differences-Sample.xlsx
I also need to highlight the entire line of data if a store number is not found on last weeks report tab.
Highlight-Differences-Sample.xlsx
ASKER
I need to locate not only the store that has data changed but which cell in the store row has changed
I need to locate not only the store that has data changed but which cell in the store row has changed
Why? You wanted to highlight the whole row in your question? Besides, how will you know which row should be matched to which other row for the cell to cell comparision without any kind of an identifier?
Why? You wanted to highlight the whole row in your question? Besides, how will you know which row should be matched to which other row for the cell to cell comparision without any kind of an identifier?
ASKER
The store # is the identifier
ASKER
and I said Highlight the changes(I need to locate not only the store that has data changed but which cell in the store row has changed) BUT highlight the entire line of data if a store number is not found on last weeks report tab.
The store # is the identifier
That is not enough since its not unique. For example Store #20 occurs 2 times on the 'Last Week' tab but once on the 'Cuerrent' tab, how would the application know which should it be matched vs?
That is not enough since its not unique. For example Store #20 occurs 2 times on the 'Last Week' tab but once on the 'Cuerrent' tab, how would the application know which should it be matched vs?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry about the dup -- On the working file the store is unique and will only be listed once.
Did you try the code?
ASKER
Worked Perectly
ASKER
Could you please take sometime to explain the code for me?
With pleasure
Sub hiliteDiff()
Dim cw As Worksheet
Dim lw As Worksheet
Set cw = Worksheets("Cuerrent")
Set lw = Worksheets("Last week")
'*****remove all colors from current week sheet
cw.UsedRange.Offset(1, 0).Interior.Pattern = xlNone
'**** loop through each row in the current week worksheet
For Each rs In cw.UsedRange.Rows
'*****This section only takes care of non-blanks in the first column of the current week worksheet
If rs.Cells(1, 1) <> "" Then
'******this checks if there is a row in last week which matches a row in current week
mr = Application.Match(rs.Cells (1, 1), lw.Range("A:A"), 0)
'******if there is no match then mr contains an error in which case the entire row is given color 65535(yellow)
If IsError(mr) Then
rs.Interior.Color = 65535
'*****if there is a match (ie mr does not have an error) then it checks individual cells (columns 2 to 4)
Else
For i = 2 To 4
'*****if the individual cells do not match then the cell is colored 255(red)
If rs.Cells(1, i) <> lw.Cells(mr, i) Then rs.Cells(1, i).Interior.Color = 255
Next i
End If
End If
Next rs
End Sub
Sub hiliteDiff()
Dim cw As Worksheet
Dim lw As Worksheet
Set cw = Worksheets("Cuerrent")
Set lw = Worksheets("Last week")
'*****remove all colors from current week sheet
cw.UsedRange.Offset(1, 0).Interior.Pattern = xlNone
'**** loop through each row in the current week worksheet
For Each rs In cw.UsedRange.Rows
'*****This section only takes care of non-blanks in the first column of the current week worksheet
If rs.Cells(1, 1) <> "" Then
'******this checks if there is a row in last week which matches a row in current week
mr = Application.Match(rs.Cells
'******if there is no match then mr contains an error in which case the entire row is given color 65535(yellow)
If IsError(mr) Then
rs.Interior.Color = 65535
'*****if there is a match (ie mr does not have an error) then it checks individual cells (columns 2 to 4)
Else
For i = 2 To 4
'*****if the individual cells do not match then the cell is colored 255(red)
If rs.Cells(1, i) <> lw.Cells(mr, i) Then rs.Cells(1, i).Interior.Color = 255
Next i
End If
End If
Next rs
End Sub
ASKER
Each rs In cw.UsedRange.Rows
The "rs" is what is throwing me along with rs.Cells(1, 1) which seems to be written as a worksheet.
The "rs" is what is throwing me along with rs.Cells(1, 1) which seems to be written as a worksheet.
rs is a row in the current worksheet. In the first instance I intended to type a rw but ended up in rs accidentally. Just used it as-is instead of correcting it.
when I use rs.cells(1,1) it considers rs to be a worksheet with number of rows equal to 1 and the number of columns equal to the number of columns used int the worksheet (cw.usedrange)
Saqib
when I use rs.cells(1,1) it considers rs to be a worksheet with number of rows equal to 1 and the number of columns equal to the number of columns used int the worksheet (cw.usedrange)
Saqib
Step two: Search each row in sheet1 for a corresponding row in sheet2 and highlight as required
Step three: Search each row in sheet2 for a corresponding row in sheet1 and highlight as required
Step four: Delete/Erase the concatenated fields
Leon