シートデータ比較

Sub Compare_Test()
'
'beforeのファイル/シート選択
'afterのファイル/シート選択
Dim BfFilePath As Variant: BfFilePath = "xxx"
Dim BfFileName As Variant: BfFileName = "xxx.xlsx"
Dim BfSheetName As Variant: BfSheetName = "before"
Dim AfFilePath As Variant: AfFilePath = "xxxx"
Dim AfFileName As Variant: AfFileName = "xxxx.xlsx"
Dim AfSheetName As Variant: AfSheetName = "after"
Dim ResultSheetName As Variant
Dim BfBookObj As Workbook ' 相手ブック
Dim BfSheetObj As Worksheet ' 相手シート
Dim AfBookObj As Workbook ' 相手ブック
Dim AfSheetObj As Worksheet ' 相手シート

Dim bf_S_Row As Integer: bf_S_Row = 3
Dim bf_S_Colom As Integer: bf_S_Colom = 1
Dim af_S_Row As Integer: af_S_Row = 3
Dim af_S_Colom As Integer: af_S_Colom = 1

Dim bf_Row_cnt As Integer
Dim af_Row_cnt As Integer
Dim check_Row_cnt As Integer: check_Row_cnt = 2
Dim check_flg As Boolean

If XXXXXX(BfFileName) = True Then
Set BfBookObj = Workbooks(BfFileName)
Set BfSheetObj = Workbooks(BfFileName).Worksheets(BfSheetName) ' シートのオブジェクトを取得

Else
Set BfBookObj = Workbooks.Open(BfFilePath & BfFileName)
Set BfSheetObj = BfBookObj.Worksheets(BfSheetName) ' シートのオブジェクトを取得

End If

If XXXXXX(AfFileName) = True Then
Set AfBookObj = Workbooks(AfFileName)
Set AfSheetObj = Workbooks(AfFileName).Worksheets(AfSheetName) ' シートのオブジェクトを取得

Else
Set AfBookObj = Workbooks.Open(AfFilePath & AfFileName)
Set AfSheetObj = AfBookObj.Worksheets(AfSheetName) ' シートのオブジェクトを取得

End If



'afterシートをafter_比較シートでコピー
ResultSheetName = AfSheetName & "_比較_" & Format(Now, "yymmdd_hhmmss")
AfBookObj.Worksheets(AfSheetName).Copy After:=AfBookObj.Worksheets(AfSheetName)
ActiveSheet.Name = ResultSheetName

'after_比較シートをアクティブ
AfBookObj.Worksheets(ResultSheetName).Activate

'afterデータ列がなくなるまでループ
af_Row_cnt = 0
Do While Cells(af_S_Row + af_Row_cnt, af_S_Colom).Value <> ""
'beforeのデータが一致するまでループ
bf_Row_cnt = 0
check_flg = False
Do While BfSheetObj.Cells(bf_S_Row + bf_Row_cnt, bf_S_Colom).Value <> ""
If BfSheetObj.Cells(bf_S_Row + bf_Row_cnt, bf_S_Colom).Value = _
Cells(af_S_Row + af_Row_cnt, af_S_Colom).Value Then

'チェック処理
For Cnt = 1 To check_Row_cnt
If BfSheetObj.Cells(bf_S_Row + bf_Row_cnt, bf_S_Colom + Cnt).Value <> _
Cells(af_S_Row + af_Row_cnt, af_S_Colom + Cnt).Value Then

Cells(af_S_Row + af_Row_cnt, af_S_Colom + Cnt).Interior.Color = RGB(255, 255, 0)
End If
Next

check_flg = True
Exit Do
End If

bf_Row_cnt = bf_Row_cnt + 1
Loop


If check_flg = False Then
'beforに該当なしの処理
For Cnt = 0 To check_Row_cnt
Cells(af_S_Row + af_Row_cnt, af_S_Colom + Cnt).Interior.Color = RGB(0, 255, 0)
Next
End If


af_Row_cnt = af_Row_cnt + 1
Loop


End Sub