Hello, So what I want is very simple, but for the life of me, I cannot figure it

Question

Hello,

So what I want is very simple, but for the life of me, I cannot figure it out. I have two sheets in the same workbook. They are formatted the same and have 2000+ rows of data. I want to compare the two sheets and highlight discrepancies between the two data sets. When I run the following macro, I get an overflow error 6 and only the very first part of the macro is completed. Any clues on what to change? The debugger highlights "If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then" to change, but I am not sure what to do.

Sub Macro3()

Dim wbk As Workbook
Set wbk = ThisWorkbook

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = wbk.Sheets(1)
Set ws2 = wbk.Sheets(2)

Dim CheckRange As Range
Set CheckRange = ws1.Range("A1:Z1000")

Dim cell As Range

CheckRange.ClearFormats

For Each cell In CheckRange

If cell.Value <> ws2.Cells(cell.Row, cell.Column).Value Then
cell.Interior.Color = RGB(200, 160, 35)
End If

Next cell

End Sub

Thanks!!

Explanation / Answer

xx should be dimmed as a variant. With no specification as you have written it, it is a variant. Dim xx is identical to dim xx as variant.

This is wrong: For Each cell In CheckRange it should be as I have coded it in my original reply:

For each xx in checkrange.cells

Try it with these changes and if it does not work, try to explain what is not working.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.