はじめに
表などでセルに点在する値のうち、重複する値のセルに色を付けて判別するプログラムを考えてみましょう。
最初は、1列のデータの重複する値を色付けするプログラムを考え、次に複数列にも対応できるようにしてみます。
For文とIf文を使ってスクラッチで作るプログラムと、ワークシート関数を利用した方法を紹介します。
プログラムの仕様
重複するデータのセルの背景に色を付けていきますが、色付けは ColorIndex を使います。
ColorIndex は56種類しかありませんが、色合いが適当にバラけていて扱いやすいためです。
尚、ColorIndex は1,2がそれぞれ黒、白となっているので3番目から使う事にします。
このため、54種類以上の重複はエラーとなります。
For文とIf文でプログラム
最初に一列分のデータの重複を見つけるプログラムです。構造としてはデータのループを二重に行いながら同じならば色を付けて良くという感じで進めていきます。
細かいノウハウについてはプログラムコードに沿って解説していきます。
Sub macro1()
Dim i As Long, j As Long
Dim lastRow As Long
Dim idx As Long
Dim flag As Boolean
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row '(1)
Range("A1:A" & lastRow).Interior.ColorIndex = xlNone ' (2)
idx = 3 '(3)
For i = 1 To lastRow - 1 '(4)
If Cells(i, "A").Interior.ColorIndex = xlNone Then '(5)
flag = False
For j = i + 1 To lastRow '(6)
If Cells(i, "A").Value = Cells(j, "A").Value Then '(7)
If flag = False Then '(8)
Cells(i, "A").Interior.ColorIndex = idx '(9)
flag = True
End If
Cells(j, "A").Interior.ColorIndex = idx '(10)
End If
Next j
If flag Then '(11)
idx = idx + 1 '(12)
End If
End If
Next i
End Sub (1) では、データの最終行を取得しています。VBAでは定番の書き方になります。
(2) データの範囲の背景色を塗りつぶしなしで初期化しています。
(3) idx はColorIndexの値を保持する変数です。初期値は3からスタートさせます。
(4) 最初のループです。lastRow – 1 として最後から2番目までとしているのは、最後は比べる必要がないからです。
(5) ここでは、背景色が塗りつぶしなしのセルを対象としています。回数が進み重複されたセルは色がついているので、ここで弾かれます。
(6) 2回目のループです。スタートするのは1回目のループの i の次の値から最後までの値で大丈夫です。
(7) セル同士の値を比較しています。値が同じなら背景色に色を付ける工程に進みます。
(8) ここではflagの確認をしています。これがないと1回目のループで比較対象となっているセルに何度も同じ背景色をセットする形になります。エラーになるというより無駄を無くすための処理になります。
※flagの効用については後程解説します。
(9) 1回目のループで対象となったセルに背景色をセットしています。
(10) 2回目のループで対象となったセルに背景色をセットしています。
(11) 2回目のループを出た後にflagを確認して背景色をセットしていた場合は、（12）で ColorIndexの値を1つ進めます。
フラグの役割について
flag は、重複した値があるかを確認するための変数です。このflagに関しては2つの役割があります。
1つは、1回目のループの値に何度も背景色をセットするのを避けてくれます。(8)
2つめは、ColorIndex を順序良く進めるためです。(11) これをしないと重複が無い場合でもColorIndex を進めることになります。
ただ、flagはなくてもそれなりに機能します。flagを使わない事でコードを簡潔に書けるので重複数が少なかったり、重複の種類が少ない場合は、簡潔なコードを優先するのも悪くない選択肢と言えるでしょう。
以下にflagを用いない例を示します。
Sub macro1a()
Dim i As Long, j As Long
Dim lastRow As Long
Dim idx As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row '(1)
Range("A1:A" & lastRow).Interior.ColorIndex = xlNone ' (2)
idx = 3 '(3)
For i = 1 To lastRow - 1 '(4)
If Cells(i, "A").Interior.ColorIndex = xlNone Then '(5)
For j = i + 1 To lastRow '(6)
If Cells(i, "A").Value = Cells(j, "A").Value Then '(7)
Cells(i, "A").Interior.ColorIndex = idx '(9)
Cells(j, "A").Interior.ColorIndex = idx '(10)
End If
Next j
idx = idx + 1 '(12)
End If
Next i
End Subワークシート関数を使用した例 【推奨】
ワークシート関数のCountifを使えば、第一引数の範囲内に第二引数の値がいくつあるかを返してくれます。これを利用すればセルの範囲内部に重複があるかを見つける事ができます。
以下のプログラムでは、セル範囲の特定にCurrentRegionを使っていますので、複数列のデータでも重複をマークすることができます。
Sub macro2()
Dim c1, c2
Dim myRange As Range
Dim idx As Long
Set myRange = Range("A1").CurrentRegion '(1)
myRange.Interior.ColorIndex = xlNone ' (2)
idx = 3 ' (3)
For Each c1 In myRange '(4)
If WorksheetFunction.CountIf(myRange, c1) > 1 _
And c1.Interior.ColorIndex = xlNone Then '(5)
For Each c2 In myRange '(6)
If c1.Value = c2.Value Then '(7)
c2.Interior.ColorIndex = idx
End If
Next c2
idx = idx + 1 '(8)
End If
Next c1
End Sub プログラムを解説します。
(1)で検索範囲を特定しています。CurrentRegionを使えば起点となるRangeから一塊のデータを範囲選択できます。
(2)では、選択範囲の背景色の塗りつぶしなしにして初期化しています。
(3) idx はColorIndexの値を保持する変数で、初期値は3からスタートさせます。
(4)は選択範囲のループになります、今回はFor Each を使って巡回させます。一つ一つのセルは c1 に格納されます。
(5)の条件分岐では、ワークシート関数の WorksheetFunction.CountIf で重複が無いかを調べています。さらに、背景色の塗りつぶしなしで既にマークされたセルは弾いています。
(6)2回目のループです。(7)で同じ値ならばセルの背景色をセットします。
(8)では、ColorIndexの値を進めています。
今回のプログラムでは、 WorksheetFunction.CountIf が重複の有無を確かめる役割を果たしてくれているのでフラグを使用しなくても簡潔なコードとなっています。
まとめ
値が重複するセルに色を付けるというプログラムは他にも、配列を使うなどいくつかのやり方があると思います。
このようなプログラムロジックは、自分で考えながらプログラムを組んでいくと実力が養われると思いますので是非挑戦してみてください。
