Data Sheet Compare Macro

marshalltj67 

The issue you're encountering seems to be related to how you're attempting to compare rows in your tables. Let's try to address each problem individually:

1. Run-time error '438' - Object doesn't support this property or method: This error occurs because you're trying to use the .Range property on a ListObject object. However, the ListObject doesn't have a .Range property. Instead, you should access the individual cells using the .DataBodyRange property of the ListObject. You've already used this correctly in your first loop. So, you should apply the same approach to your second loop.
2. Compile Error: Argument not optional: This error occurs because the Join function requires two arguments - the array you want to join and the delimiter. It seems you're missing the delimiter argument in your Join function calls.

Here's how you can maybe fix your second loop and address these issues:

Sub CompareTwoTables()

    Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject
    Dim rngCell As Range, rowToday As ListRow, rowYesterday As ListRow
    Dim foundRow As Boolean
    
    ' Reference Tables
    Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData")
    Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData")
    
    ' Loop 1: Highlight changed cells
    For Each rngCell In TodayDataTable.DataBodyRange
        If rngCell.Value <> YesterdayDataTable.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
            rngCell.Interior.Color = vbYellow
            rngCell.Font.Color = vbRed
        End If
    Next rngCell
    
    ' Loop 2: Highlight new rows
    For Each rowToday In TodayDataTable.ListRows
        foundRow = False
        
        For Each rowYesterday In YesterdayDataTable.ListRows
            If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then
                foundRow = True
                Exit For
            End If
        Next rowYesterday
        
        ' If the row doesn't exist in the "Yesterday Data" table, it's a new row
        If Not foundRow Then
            rowToday.Range.Interior.Color = vbYellow
            rowToday.Range.Font.Color = vbGreen
        End If
    Next rowToday

End Sub

In the second loop, I've made the following changes:

• Changed rowToday and rowYesterday from Range objects to ListRow objects, as they represent entire rows in your tables.
• Ensured that the delimiter argument (|) is included in the Join function calls.

Try running this updated code, and it should resolve the errors you've encountered. The text, steps and the code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.