locked
Compare 2 excel sheets and highlight wrong values RRS feed

  • Question

  • Greeting awesome people, I am new to VBA for excel and currently, I want to compare the values of two excel sheets and highlight the wrong values. Sheet1 contains the list of correct value and Sheet2 contains the value to be validated. However, my code is highlighting every single cell. How can I correct the code? Please help. 

    Sub RunningCompare()
        Call compareSheets("Sheet1", "Sheet2")
    End Sub
    
    
    Sub compare(shtSheet1 As String, shtSheet2 As String)
        Dim cell As Range
        Dim RowsCount As Long
        Dim aCell As Range
        Dim GreenRows As Range
        Dim CountColorValue As Integer
        Dim TotalCount As Integer
        Dim LastRow As Long
    
        Set aCell = Sheet2.Range("A1")
        For Each cell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
            If Not cell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(cell.Row, cell.Column).Value Then
                cell.EntireRow.Interior.Color = vbGreen
            End If
        Next
    
        Do While Not IsEmpty(aCell)
            If aCell.Interior.Color = vbGreen Then 
                If GreenRows Is Nothing Then
                    Set GreenRows = aCell.EntireRow
                Else
                    Set GreenRows = Union(GreenRows, aCell.EntireRow)
                End If
            End If
            Set aCell = aCell.Offset(1, 0)
        Loop
    
        If Not GreenRows Is Nothing Then
            GreenRows.Copy Sheet3.Range("A1")
        End If
    
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Debug.Print LastRow
    
        MsgBox LastRow & "Row of error", vbInformation
    
        ActiveWorkbook.Sheets(shtSheet2).Select
    End Sub

    Thank you very much.

    Tuesday, July 24, 2018 1:56 PM