VBA-Comparing two sheets and highlighting differences

[Janko32]

Hello,

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 String, shtSheet2 As String)

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 ActiveWorkbook.Worksheets(shtSheet2).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(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 two 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.

I thought that this would work but I'm getting a "Subscript out of range error" in this line of my code.

For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange

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


Also, not sure if this is cross-posting but better to be safe.

VBA-WorkBook_Change

Cross-posted with responses at https://www.mrexcel.com/board/threads/vba-workbook_sheetchange.1189686/ Hello, I am very new to VBA so this might be a dumb question. For our company, we have an Excel workbook that has multiple sheets. We have a Master sheet that all the changes will...

www.excelforum.com

VBA - Workbook_SheetChange

Hello, I am very new to VBA so this might be a dumb question. For our company, we have an Excel workbook that has multiple sheets. We have a Master sheet that all the changes will be made to. The other sheets are Branches of the master that have linked cells to the master. I know that there...

www.mrexcel.com

 

[Janko32]

...changed my code

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

Now getting a Byref argument type mismatch in this line...

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