Comparing spreadsheet - VBA almost working..

[cerebrolele]

Hi,

I am using a code to compare two spreadsheet.
One of the function is to copy the modified records in a different worksheet, highlighting changes element in red.
The script works, but ONLY when the cell is modified to "blank"(no value), I get coloured in red the upper cell instead of the correct one.

Here the script, any help?

Sub GDV()
    Dim WsA As Worksheet, WsB As Worksheet, WsC As Worksheet, WsD As Worksheet, WsE As Worksheet
    Dim rFind As Range, c As Range
    Dim I As Integer, ColCnt As Integer
     
    Set WsA = Worksheets("OldExport")
    Set WsB = Worksheets("NewExport")
    Set WsC = Worksheets("Changes")
    Set WsD = Worksheets("PosDeleted")
    Set WsE = Worksheets("PosAdded")
     
    ColCnt = WsA.Cells(1, Columns.Count).End(xlToLeft).Column
     
    With CreateObject("Scripting.Dictionary")
        For Each c In WsA.Range("A2", WsA.Range("A" & Rows.Count).End(xlUp))
            If Not .exists(c.Value) Then
                .Add c.Value, False
                Set rFind = WsB.Columns(1).Find(What:=c.Value, LookIn:=xlValues)
                If Not rFind Is Nothing Then
                    For I = 1 To ColCnt
                        If Not c.Offset(, I - 1) = WsB.Cells(rFind.Row, I) Then
                            If .Item(c.Value) = False Then
                                rFind.Resize(1, ColCnt).Copy WsC.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                .Item(c.Value) = True
                            End If
                            WsC.Cells(Rows.Count, I).End(xlUp).Interior.ColorIndex = 3
                        End If
                    Next I
                Else
                    MsgBox c.Value & " PosID has been canceled!"
                    c.Resize(1, ColCnt).Copy WsD.Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            End If
        Next c
        For Each c In WsB.Range("A2", WsB.Range("A" & Rows.Count).End(xlUp))
            If Not .exists(c.Value) Then
                MsgBox c.Value & " PosID has been added!"
                c.Resize(1, ColCnt).Copy WsE.Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        Next c
    End With
End Sub

[dazwm]

Please do not multi-post.

http://www.excelforum.com/excel-prog...readsheet.html

http://www.ozgrid.com/forum/showthread.php?t=166275

[cerebrolele]

I am sorry, I am trying to solve the issue and I am looking for help around.

[dazwm]

I am sorry, I am trying to solve the issue and I am looking for help around.

The problem with multi posting is you get several people working on giving you the answer on all different websites. Not etiquette!

Cross posting without providing a link to the other thread is a violation of the Forum Rules. We prefer you do not cross post, but when doing so you are obligated to provide the link per your agreement to abide the rules of this forum. (All Excel forums have a similar rule about cross posting.)

[cerebrolele]

to who may be interested
I found a solution (workaround) here:
h**p://w*w.excelforum.com/excel-programming/836871-vba-comparison-spreadsheet.html?p=2818323&viewfull=1#post2818323

(cannot post links)