Macros Question

I want to compare two workbooks. If information was included in a row on the old log, and then erased on the new log, I want to see it highlighted. Thanks!
Member_2_7970183Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please attach a workbook that includes an example of what the output should look like.
1
xtermieCommented:
try this macro..you can substitute for your sheet names and column/row constants

Sub HighlightDuplicateDifferences5()
    Const ID_COL As Integer = 5     'OrderNo is in the fifth column
    Const ID_COL2 As Integer = 4    'Item is in the fourth column
    Const NUM_COLS As Integer = 21  'how many columns are being compared?
    Const NUM_ROWS As Integer = 7   'how many rows in Sheet1
    Const NUM_ROWS2 As Integer = 7  'how many rows in Sheet2
    
    Dim shtNew As Excel.Worksheet, shtOld As Excel.Worksheet
    Dim rwNew As Range, rwOld As Range, Rng, f As Range
    Dim x As Integer, Id
    Dim prd1, prd2 As String

    Set shtNew = ActiveWorkbook.Sheets("Sheet2")
    Set shtOld = ActiveWorkbook.Sheets("Sheet1")
    
    Set rwNew = shtNew.Rows(2) 'first order on "current" sheet
      
    Do While rwNew.Cells(ID_COL).Value <> ""
        Id = rwNew.Cells(ID_COL).Value          'Set SO we are looking for
        prd1 = rwNew.Cells(ID_COL2).Value       'Set Item we are looking for
        For i = 1 To NUM_ROWS
             If (shtOld.Cells(i, ID_COL).Value = Id) And (shtOld.Cells(i, ID_COL2).Value = prd1) Then
                myrow = i
                Exit For
            End If
        Next i
       
        Set f = shtOld.UsedRange.Rows(myrow)

        If Not f Is Nothing Then
            Set rwOld = f.EntireRow
            prd2 = rwOld.Cells(ID_COL2).Value
            If prd1 = prd2 Then
            For x = 1 To NUM_COLS
                    If rwNew.Cells(x).Value <> rwOld.Cells(x).Value Then
                          If IsEmpty(rwNew.Cells(x).Value) Then rwNew.Cells(x).Interior.Color = RGB(204, 236, 255) 'value is empty in new sheet
                          rwNew.Cells(x).Font.Color = RGB(255, 0, 0)  'value is different
                    Else
                         rwNew.Cells.Interior.ColorIndex = xlNone
                    End If
                Next x
                End If
         End If
         Set rwNew = rwNew.Offset(1, 0) 'next row to compare
    Loop
'Check for New records
    Set rwNew = shtNew.Rows(2) 'first order on "current" sheet
    Do While rwNew.Cells(ID_COL).Value <> ""
        Id = rwNew.Cells(ID_COL).Value
        Set f = shtOld.UsedRange.Columns(ID_COL).Find(Id, , xlValues, xlWhole)
        If f Is Nothing Then rwNew.Cells(ID_COL).Interior.Color = vbGreen 'new order no
        Set rwNew = rwNew.Offset(1, 0) 'next row to compare
    Loop
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xtermieCommented:
question answered in macro provided
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.