Solved

Macros Question

Posted on 2016-08-05
3
45 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 46

Expert Comment

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

Accepted Solution

by:
xtermie earned 500 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 17

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
text replacement instead of two macros only one macro 2 19
Excel Formula 4 28
Freeze Panes Solution 6 28
Excel 2010 - Populate a formula in multiple cells 6 23
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now