Data Sheet Compare Macro

marshalltj67 

It seems like you're on the right track, as far as I can see it. Let's address your requirements:

1. Highlighting Changed Cells in Yellow and Formatting Font to Red: Your existing code snippet inside the loop should work for this requirement. It compares the cell values between the two tables and highlights the cell yellow if they are different, then changes the font color to red.
2. Highlighting New Rows in Yellow and Formatting Font to Green: For this, you need to identify rows that exist in the "Today Data" table but not in the "Yesterday Data" table. You can achieve this by looping through each row in the "Today Data" table and checking if the corresponding row exists in the "Yesterday Data" table. If it doesn't, then it's a new row and you can highlight the entire row in yellow and format the font to green.

Here's how you can maybe implement it:

Vba code is untested.

Sub CompareTwoTables()

    Dim tbl1 As ListObject, tbl2 As ListObject
    Dim rngCell As Range, rowToday As Range
    Dim foundRow As Boolean
    
    ' Set references to the tables
    Set tbl1 = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData")
    Set tbl2 = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData")
    
    ' Loop through each cell in the "Today Data" table
    For Each rngCell In tbl2.DataBodyRange
        ' Compare cell values between tables
        If rngCell.Value <> tbl1.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
            ' Highlight the changed cell in yellow
            tbl2.Cells(rngCell.Row, rngCell.Column).Interior.Color = vbYellow
            ' Change font color to red
            tbl2.Cells(rngCell.Row, rngCell.Column).Font.Color = vbRed
        End If
    Next rngCell
    
    ' Loop through each row in the "Today Data" table
    For Each rowToday In tbl2.ListRows
        foundRow = False
        ' Check if the row exists in the "Yesterday Data" table
        For Each rowYesterday In tbl1.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
            ' Highlight the entire row in yellow
            rowToday.Range.Interior.Color = vbYellow
            ' Format font color to green
            rowToday.Range.Font.Color = vbGreen
        End If
    Next rowToday
    
End Sub

This code will iterate through each cell in the "Today Data" table, comparing it with the corresponding cell in the "Yesterday Data" table. If a difference is found, it highlights the cell in yellow and changes the font color to red. Then, it iterates through each row in the "Today Data" table to check if it exists in the "Yesterday Data" table. If not, it highlights the entire row in yellow and formats the font color to green.