Solved

Macros Question

Posted on 2016-08-05
3
53 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
[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
  • 2
3 Comments
 
LVL 47

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 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 18

Expert Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

726 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