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.
