Option Base 1
'=================================================
Sub Test1()
'A列・B列にサンプル作成・各列30000行
Dim WS1 As Worksheet
Dim buf1(30000, 1) As Long
Dim buf2(30000, 1) As Long
Dim f As Long
Set WS1 = Worksheets("Sheet1")
For f = 1 To 30000
buf1(f, 1) = Int(Rnd() * 3 + 1)
buf2(f, 1) = Int(Rnd() * 3 + 1)
Next
With WS1
.Range("A1").Resize(30000, 1).Value = buf1
.Range("B1").Resize(30000, 1).Value = buf2
End With
End Sub
'==================================================
Sub Test2()
'A列とB列を比較して、同じなら"○",違えば"×"
Dim WS1 As Worksheet
Dim buf1 As Variant, buf2 As Variant
Dim myAns(30000, 1) As Variant
Dim f As Long, ff As Long
Set WS1 = Worksheets("Sheet1")
With WS1
buf1 = .Range("A1:A30000").Value
buf2 = .Range("B1:B30000").Value
End With
For f = 1 To 30000
If buf1(f, 1) = buf2(f, 1) Then
myAns(f, 1) = "○"
Else
myAns(f, 1) = "×"
End If
Next
WS1.Range("C1").Resize(30000, 1).Value = myAns
End Sub
Sub Test()
Dim f As Long, ff As Long
For ff = 1 To 26
For f = 1 To 100
If Worksheets("A").Cells(f, ff).Value = _
Worksheets("B").Cells(f, ff).Value Then
Worksheets("CHK").Cells(f, ff).Interior.ColorIndex = 28
Else
Worksheets("CHK").Cells(f, ff).Interior.ColorIndex = 3
Worksheets("CHK").Cells(f, ff).Value = "不一致"
End If
Next
Next
End Sub
Sub A_test()
Dim f As Long, ff As Long
Dim myC As Boolean
Dim myMsg As String
myC = False
For ff = 1 To 26
For f = 1 To 100
If Worksheets("A").Cells(f, ff).Value = _
Worksheets("B").Cells(f, ff).Value Then
Worksheets("CHK").Cells(f, ff).Interior.ColorIndex = 28
Else
Worksheets("CHK").Cells(f, ff).Interior.ColorIndex = 3
Worksheets("CHK").Cells(f, ff).Value = "不一致"
myC = True
End If
Next
Next
Select Case myC
Case True: myMsg = "不一致"
Case False: myMsg = "一致"
End Select
Sub Macro1()
Dim co1 As Long, co2 As Integer
Dim i As Long, j As Long, k As Integer
Dim ck As String
Dim myt As Single
Dim s2 As Worksheet
Set s2 = Sheets("sheet2")
With Sheets("sheet1")
myt = Timer 'タイマー設置
co2 = 1 '列数の初期値
For k = 1 To 255
'初期値と対象の最右列数と比較
If co2 < .Range("a" & k).End(xlToRight).Column Then
co2 = .Range("a" & k).End(xlToRight).Column
End If
Next k
For i = 1 To co2 '列数
co1 = .Cells(65536, i).End(xlUp).Row '行数の確定
If .Cells(65535, i) <> "" Then '行が65536行の場合帰値が1なので1行か65536行か区別
co1 = 65536 '65535行に数値があったら65536行を入れる
End If
For j = 1 To co1 '対象セル同士が不一致なら下記の処理
If .Cells(j, i) <> s2.Cells(j, i) Then
ck = ck & "," & .Cells(j, i).Address
.Cells(j, i).Interior.ColorIndex = 5 '違う部分に色5を指定
End If
Next j
Next i
MsgBox "不一致セルは" & ck
MsgBox "かかった時間は" & Timer - myt
End With
End Sub