Data Sheet Compare Macro

Question

Good Afternoon All,&nbsp;I am very new to Macros and VBA and I am currently writing my first Macro to compare two data sheets with a specific format. I currently have two sheets with data outputted as a tables; this was done because the data updates daily so I have a query to reference a "yesterday's data" excel workbook file and a query to reference a "today's data" excel workbook file so all the data is in the same workbook in different sheets. These tables are then referenced to two separate sheets to get data out of the table format for the Macro to work.&nbsp;I currently have the Macro code to compare the two sheets and highlight the cell red if it has changed.&nbsp;Please see the attached excel workbook and the following code:&nbsp;&nbsp;Sub CompareTwoSheets()&nbsp;Dim dataRng As Range, selCell As Range&nbsp;Worksheets(1).Activate&nbsp;Set dataRng = ActiveCell.CurrentRegion&nbsp;For Each selCell In dataRng&nbsp;&nbsp;&nbsp;&nbsp; If selCell.Value &lt;&gt; Worksheets(2).Range(selCell.Address).Value Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets(2).Range(selCell.Address).Interior.Color = vbRed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If&nbsp;&nbsp;&nbsp;Next selCell&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Sub&nbsp;3 Questions:1) Is there a way I can write this to reference the tables instead of the separate excel sheets?&nbsp;2) Is there a way I can configure the format so instead of it highlighting the changed cell red, it changes the entire row to "Font Text - Red" and then highlights the change in "Yellow"?&nbsp;3) Is there a way I can add a format so if a new row is added it changes the entire row to "Font Text - Green"&nbsp;I am running Microsoft Office Professional Plus 2016 on an offline system and do not have the capability to upgrade - Queries were developed on the offline system (attached workbook is a reference)&nbsp;I greatly appreciate the support - thank you!&nbsp;Marshall

nikolinode · Answer

marshalltj67&nbsp;&nbsp;Here is a code that is untested, maybe it will help you. &nbsp;Sub CompareTwoTables()
    Dim tbl1 As ListObject, tbl2 As ListObject
    Dim rngCell As Range
    
    ' Set references to the tables
    Set tbl1 = Worksheets("Sheet1").ListObjects("Table1")
    Set tbl2 = Worksheets("Sheet2").ListObjects("Table2")
    
    ' Loop through each cell in the first table
    For Each rngCell In tbl1.DataBodyRange
        ' Compare cell values between tables
        If rngCell.Value &lt;&gt; tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
            ' Highlight the entire row in yellow
            tbl1.ListRows(rngCell.Row - tbl1.HeaderRowRange.Row + 1).Range.Interior.Color = vbYellow
            tbl2.ListRows(rngCell.Row - tbl1.HeaderRowRange.Row + 1).Range.Interior.Color = vbYellow
            
            ' Change font color to red
            rngCell.Font.Color = vbRed
            tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Font.Color = vbRed
        End If
    Next rngCell
End Sub&nbsp;For security reasons, I don't open any external files at the moment. If it is not what you have in mind, please provide more details about what you would like to achieve, step by step if possible, so that I can understand the translation .

marshalltj67 · Answer

NikolinoDE&nbsp;&nbsp;Please see the attached photos for what I am trying to accomplish with the Macro. Basically, I have a table that will always stay unformatted since it is the "Yesterday Data" since I want to highlight any changes. Once I run the query, the "Today Data" table will shift to a "Yesterday Data" sheet as a table and the query will pull the new data and place it in a "Today Data" sheet as a table. At that point, I will need to highlight if there are any changes in the following formats:&nbsp;New row added to the table: Highlight the entire row "Yellow" and format text as "Green"Update to data: Highlight the specific cell that changed "Yellow" and format the entire row text as "Red"&nbsp;I hope this helps! I tried running your code and it worked fine but not for this comparison application as I need to visually analyze the changed or newly added data on one sheet.&nbsp;Here is my updates to the code... I am trying to figure out how to perform a FOR IF loop for if a new row is added but I can't find anything on how to do it.&nbsp;Sub CompareTwoTables()

Dim tbl1 As ListObject, tbl2 As ListObject
    
    Dim rngCell As Range
    
    ' 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 "Yesterday Data" table
    For Each rngCell In tbl2.DataBodyRange
    
        ' Compare cell values between tables
        If rngCell.Value &lt;&gt; tbl1.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
        
       '  Highlights cell that is differnt "Yellow"
       tbl2.Range(selCell.Address).Interior.Color = vbYellow

' Change font color to red
        tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Font.Color = vbRed
            
        End If

' Loop for if a new row is added
    For Each Rng In CellIntbl2.DataBodyRange
    
        If
        tbl2.ListRows(rngCell.Row - tbl1.HeaderRowRange.Row + 1).Range.Interior.Color = vbYellow
        tbl2.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Font.Color = vbGreen
        End If

Next rngCell
    
End Sub&nbsp;Thank you so much! I greatly appreciate the help!&nbsp;Marshall&nbsp;

nikolinode · Answer

marshalltj67&nbsp;It seems like you're on the right track, as far as I can see it. Let's address your requirements: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.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 &lt;&gt; 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 SubThis 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.

marshalltj67 · Answer

For the first loop I am getting an "Run-time error '438' - Object doesn't support this property or method" error and for the second loop I am getting a "Compile Error: Argument not optional" errorDoes this have to do with how the initial Dim code is written for setting the initial ranges?

marshalltj67 · Answer

NikolinoDE&nbsp;Note. I changed the "set" table to "YesterdayDataTable" and "TodayDataTable" to avoid confusion:&nbsp;I was able to figure out the first loop with this code:    For Each rngCell In TodayDataTable.DataBodyRange
    
        If rngCell.Value &lt;&gt; YesterdayDataTable.Range(rngCell.row, rngCell.Column).Value Then
        
            TodayDataTable.Range(rngCell.row, rngCell.Column).Interior.Color = vbYellow
            
            TodayDataTable.Range(rngCell.row, rngCell.Column).EntireRow.Font.Color = vbRed
                
        End If
        
    Next rngCellI am still having trouble with the second loop for finding if a row as been added. Was rowYesterday supposed to be in the defined as a range along with rowToday? Here is what I have as a whole and I keep getting the attached error for "Compile Error - Argument Not Optional". I think the compile error is happening in the following code:"If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then"&nbsp;&nbsp;Thank you!&nbsp;Sub CompareTwoTables()

Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject
    Dim rngCell As Range, rowToday As Range, rowYesterday As Range
    Dim foundRow As Boolean
    
   ' Reference Tables
    Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData")
    Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData")
    
    ' Loop 1 -
    
    For Each rngCell In TodayDataTable.DataBodyRange
    
        If rngCell.Value &lt;&gt; YesterdayDataTable.Range(rngCell.row, rngCell.Column).Value Then
        
            TodayDataTable.Range(rngCell.row, rngCell.Column).Interior.Color = vbYellow
            
            TodayDataTable.Range(rngCell.row, rngCell.Column).EntireRow.Font.Color = vbRed
                
        End If
        
    Next rngCell
    
    ' Loop 2 -
    
        ' Loop through each row in the "Today Data" table
    For Each rowToday In TodayDataTable.ListRows
    
        foundRow = False
        
        ' Check if the row exists in the "Yesterday Data" table
        
        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
        
            ' 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&nbsp;

Forum Discussion

Data Sheet Compare Macro

9 Replies

Resources