+ Reply to Thread
Results 1 to 10 of 10

VBA to compare and highlight differences

Hybrid View

  1. 08-03-2013, 11:55 AM #1
    REFisher14
    REFisher14 is offline
    Registered User
    Join Date
    08-01-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    VBA to compare and highlight differences

    Hi Everyone! I haven't been able to find what I'm looking for so maybe someone might be able to help. I have to worksheets I get from two different dbases do the output is slightly different in each. What I need is simple, (I think). Just need to get a VBA that looks from Sheet1 to Sheet2 for a doc number. If it's not on Sheet 2, simply highlight it BLUE on Sheet, AND then looks from Sheet2 to Sheet1, and if it's not on Sheet1, then highlight the entry on Sheet2 GREEN.

    I.E.

    SHEET1 SHEET2
    COL A COL A

    11111 AAAAA
    22222 BBBBB
    33333 11111
    AAAAA 44444


    Thank All!!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-03-2013, 03:33 PM #2
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: VBA to compare and highlight differences

    Perhaps a macro like this?

    Option Explicit

Sub ChkVal()
Dim cell As Range

Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Name = "data1"
Sheets("Sheet2").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Name = "data2"

Sheets("Sheet1").Activate
For Each cell In Range("data1")
If Not IsError(Application.VLookup(cell, Range("data2"), 1, False)) Then

Else
    cell.Interior.Color = 15773696
End If

Next

Sheets("Sheet2").Activate
For Each cell In Range("data2")
If Not IsError(Application.VLookup(cell, Range("data1"), 1, False)) Then

Else
    cell.Interior.Color = 65280
End If

Next

With ThisWorkbook
    .Names("data1").Delete
    .Names("data2").Delete
End With

End Sub
    Alf
    Reply With QuoteRegister To Reply
  3. 08-03-2013, 03:52 PM #3
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to compare and highlight differences

    Or this?

    Sub REF(): Dim w1 As Worksheet, w2 As Worksheet
Dim A1 As Range, A2 As Range, r1 As Long, r2 As Long
Set w1 = Sheets("Sheet1"): Set w2 = Sheets("Sheet2")
r1 = w1.Range("A" & Rows.Count).End(xlUp).row
r2 = w2.Range("A" & Rows.Count).End(xlUp).row

Sheet1:
For Each A1 In w1.Range("A2:A" & r1)
If A1 = "" Then GoTo Sheet2
For Each A2 In w2.Range("A2:A" & r2)
If A1 = A2 Then GoTo GetNext
Next
A1.Interior.ColorIndex = 8
GetNext: Next

Sheet2:
For Each A2 In w2.Range("A2:A" & r2)
If A2 = "" Then Exit Sub
For Each A1 In w1.Range("A1:A" & r1)
If A1 = A2 Then GoTo GetAnother
Next
A2.Interior.ColorIndex = 4
GetAnother: Next
End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  4. 08-03-2013, 04:04 PM #4
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: VBA to compare and highlight differences

    Cross posted:http://www.ozgrid.com/forum/showthread.php?t=181455
    Reply With QuoteRegister To Reply
  5. 05-16-2014, 07:37 AM #5
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare and highlight differences

    Came across this old thread.

    Can this code be easily adapted to do the same but rather than just comparing column A to compare A:E?
    Reply With QuoteRegister To Reply
  6. 05-16-2014, 01:47 PM #6
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to compare and highlight differences

    Maybe:

    Sub REF(): Dim w1 As Worksheet, w2 As Worksheet
Dim A1 As Range, A2 As Range, r1 As Long, r2 As Long
Set w1 = Sheets("Sheet1"): Set w2 = Sheets("Sheet2")
r1 = w1.Range("A" & Rows.Count).End(xlUp).row
r2 = w2.Range("A" & Rows.Count).End(xlUp).row

Sheet1:
For Each A1 In w1.Range("A2:E" & r1)
If A1 = "" Then GoTo Sheet2
For Each A2 In w2.Range("A2:E" & r2)
If A1 = A2 Then GoTo GetNext
Next
A1.Interior.ColorIndex = 8
GetNext: Next

Sheet2:
For Each A2 In w2.Range("A2:E" & r2)
If A2 = "" Then Exit Sub
For Each A1 In w1.Range("A1:E" & r1)
If A1 = A2 Then GoTo GetAnother
Next
A2.Interior.ColorIndex = 4
GetAnother: Next
End Sub
    but - you should start your own thread and post a sample book
    Reply With QuoteRegister To Reply
  7. 05-19-2014, 03:31 AM #7
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare and highlight differences

    works perfectly thanks xladept
    Reply With QuoteRegister To Reply
  8. 05-19-2014, 05:01 AM #8
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare and highlight differences

    applied the code to a larger data set and the compare doesn't seem to flag up the differences. Can't quite figure out why...

    Are there any limitations with the code which would only flag up a portion of the differences or none at all with the code running through without errors?

    If i can't find an answer i'll post up a separate thread
    Reply With QuoteRegister To Reply
  9. 05-19-2014, 08:51 AM #9
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare and highlight differences

    posted up a new post here - http://www.excelforum.com/excel-prog...ting-code.html
    Reply With QuoteRegister To Reply
  10. 05-19-2014, 03:18 PM #10
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA to compare and highlight differences

    You're welcome and thanks for the rep! I'll look at the new post later.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare two lists and highlight the differences
    By jsmo in forum Excel General
    Replies: 3
    Last Post: 08-28-2012, 10:27 PM
  2. Compare 2 Workbooks and Highlight Differences
    By LabanM in forum Excel General
    Replies: 3
    Last Post: 10-19-2010, 11:34 AM
  3. Compare two lists and highlight differences.
    By delgadotb in forum Excel General
    Replies: 1
    Last Post: 03-30-2009, 03:24 PM
  4. [SOLVED] Compare Two Worksheets:highlight the differences
    By Jim in forum Excel General
    Replies: 1
    Last Post: 10-11-2005, 04:05 PM
  5. How do I compare 2 sets of data and highlight differences?
    By Perplexed1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2005, 08:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1