Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Highlight Difference between two Sheets in the same workbook?

Posted on 2011-03-25
14
Medium Priority
?
678 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bearblack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 35216219
Step one: Concatenate all data for each row for both sheets.
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
0
 
LVL 2

Author Comment

by:bearblack
ID: 35217627
I need to locate not only the store that has data changed but which cell in the store row has changed
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35217685
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:bearblack
ID: 35217850
The store # is the identifier
0
 
LVL 2

Author Comment

by:bearblack
ID: 35217862
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.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35218065
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?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 35227642
This code is based on what I have understood. This will compare each of current week row with the first matching row from Last week.

Saqib
Sub hiliteDiff()
Dim cw As Worksheet
Dim lw As Worksheet
Set cw = Worksheets("Cuerrent")
Set lw = Worksheets("Last week")
cw.UsedRange.Offset(1, 0).Interior.Pattern = xlNone
For Each rs In cw.UsedRange.Rows
If rs.Cells(1, 1) <> "" Then
mr = Application.Match(rs.Cells(1, 1), lw.Range("A:A"), 0)
If IsError(mr) Then
rs.Interior.Color = 65535
Else
For i = 2 To 4
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

Open in new window

0
 
LVL 2

Author Comment

by:bearblack
ID: 35237720
Sorry about the dup -- On the working file the store is unique and will only be listed once.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35238023
Did you try the code?
0
 
LVL 2

Author Closing Comment

by:bearblack
ID: 35242480
Worked Perectly
0
 
LVL 2

Author Comment

by:bearblack
ID: 35242495
Could you please take sometime to explain the code for me?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35243786
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
0
 
LVL 2

Author Comment

by:bearblack
ID: 35244068
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35244292
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question