[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macros Question

Posted on 2016-08-05
3
Medium Priority
?
67 Views
Last Modified: 2016-08-27
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!
0
Comment
Question by:Member_2_7970183
  • 2
3 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41744820
Please attach a workbook that includes an example of what the output should look like.
1
 
LVL 18

Accepted Solution

by:
xtermie earned 2000 total points (awarded by participants)
ID: 41746756
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
 
LVL 18

Expert Comment

by:xtermie
ID: 41772813
question answered in macro provided
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

829 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