VBA-Comparing two sheets and highlighting differences

Janko32

New Member
Joined
Dec 7, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.

VBA 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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Janko32

New Member
Joined
Dec 7, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
...changed my code

VBA 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...

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,406
Messages
5,831,430
Members
430,070
Latest member
Renske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top