VBA 根本不是我的強項，但我試圖了解它以幫助我的公司。
這是我的代碼，然后我會嘗試解釋我的問題。
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
我不知道這是否正確，但我想要做的是當作業簿打開時……將兩張作業表存盤到兩個不同的變數中……
然后，當 master 中的值發生更改時，將更改的作業表設定為兩個新變數，然后呼叫比較表函式。
比較作業表函式將原始作業表變數與臨時作業表變數進行比較，并以橄欖綠色突出顯示差異。
如果有人對如何解決此錯誤有任何想法以及如何實施我正在嘗試做的事情的任何想法，請隨時回復。
謝謝，
泰勒
更新
好的，我更改了代碼，但現在我在這一行中發現 Byref 引數型別不匹配。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
uj5u.com熱心網友回復：
將 Eth 表上的開始值存盤在一個陣列中。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/377337.html