Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 06-06-2017, 06:13 PM
Logit Logit is offline Comparing excel sheets Windows 10 Comparing excel sheets Office 2007
Expert
 
Join Date: Jan 2017
Posts: 591
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
Code:
Option Explicit

  Sub Dups()  'Compares data Sheet1 & Sheet2 ... Highlights dupes on Sheet2
        Dim Rng As Range, Dn As Range
        With Sheets("Sheet1")
            Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
        End With
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
            For Each Dn In Rng
                .Item(Dn.Value & Dn.Offset(, 1).Value) = Empty
            Next
            With Sheets("Sheet2")
                Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
            End With
            For Each Dn In Rng
                If .exists(Dn.Value & Dn.Offset(, 1).Value) Then
                    'Dn.Resize(, 2).Interior.Color = vbYellow
                    Dn.Offset(0, 2).Value = "True"
                Else
                    Dn.Offset(0, 2).Value = "False"
                End If
            Next
        End With
    End Sub
Attached Files
File Type: xlsm Dupes Find and Mark 2 Sheets.xlsm (18.7 KB, 13 views)
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing excel sheets new to VBA help comparing two sheets mlttkw Excel Programming 1 04-21-2016 08:32 AM
HELP - comparing spreadsheets in excel 2010 ali212006 Excel 1 04-03-2014 04:04 AM
Comparing two excel worksheets SaneMan Excel 1 06-27-2012 07:52 PM
Comparing excel sheets Excel sheets ubns Excel 4 04-15-2012 10:48 PM
Comparing Data - MS Excel ramadevidokkuud Excel 1 05-19-2011 05:52 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft