VBA-Comparing two sheets and highlighting the differences-CodePudding

VBA is not at all my strength but I am trying to understand it to help my company.

Here is my code and then I'll try to explain my question.

    Sub compareSheets(shtSheet1 As Worksheet, shtSheet2 As Worksheet)
    
    Dim mycell As Range
    Dim mydiffs As Integer
    
    'For each cell in sheet2 that is not the same in Sheet1, color it olive green
    For Each mycell In shtSheet2.UsedRange
        If Not mycell.Value = shtSheet1.Cells(mycell.Row, mycell.Column).Value Then
            mycell.Interior.Color = RGB(216, 288, 188)
        End If
    Next
    
    ActiveWorkbook.Sheets(shtSheet2).Select
    
    End Sub
    
    Private Sub Workbook_Open()
        Dim master As Worksheet
        Dim eth As Worksheet
        
        Set master = Sheets("Master")
        Set eth = Sheets("eth")
    End Sub
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
        Dim masterTemp As Worksheet
        Dim ethTemp As Worksheet
        
        Set masterTemp = Sheets("Master")
        Set ethTemp = Sheets("eth")
        
        Call compareSheets(master, masterTemp)
        Call compareSheets(eth, ethTemp)
    End Sub

I don't know if this is right but what I'm trying to do is when the workbook is open...store two sheets to two different variables...

Then, when a value is changed in master, set the changed worksheets to two new variables and then call the compare sheets function.

The compare sheets function will compare the original Worksheet variable to the temp Worksheet variable and highlight the differences in olive green.

If anyone has an idea on how to fix this error as well as any ideas on how to implement what I'm trying to do, please don't hesitate to respond.

Thanks,

Tyler

UPDATE

Okay, I changed my code but now I'm getting a Byref argument type mismatch in this line.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

CodePudding user response：

Store the opening values on the Eth sheet in an array.

Option Explicit
Dim arEthOpen, addrOpen As String

Private Sub Workbook_Open()

    Dim r As Long, c As Long
    With Sheets("Eth")
        With .UsedRange
            r = .Row   .Rows.Count - 1
            c = .Column   .Columns.Count - 1
            addrOpen = .Address
        End With
        arEthOpen = .Cells(1, 1).Resize(r, c).Value2
    End With
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name <> "Master" Then Exit Sub
    ' color Master
    Target.Interior.Color = RGB(216, 288, 188)
    
    ' changes to Eth
    Dim arEth, r As Long, c As Long
    With Sheets("Eth")
        With .UsedRange
            r = .Row   .Rows.Count - 1
            c = .Column   .Columns.Count - 1
            If r > UBound(arEthOpen) Or c > UBound(arEthOpen, 2) Then
                MsgBox "Change to Used Range on Eth Sheet " & _
                "was " & addrOpen & " now " & .Address, vbCritical, "Warning"
                r = UBound(arEthOpen)
                c = UBound(arEthOpen, 2)
            End If
        End With
        arEth = .Cells(1, 1).Resize(r, c).Value2
        
        For r = 1 To UBound(arEth)
            For c = 1 To UBound(arEth, 2)
                If arEth(r, c) <> arEthOpen(r, c) Then
                    .Cells(r, c).Interior.Color = RGB(216, 288, 188)
                End If
            Next
        Next
        
    End With
End Sub