入力規則に違反したセルの件数と位置をVBAで取得・可視化する方法

2025年7月21日

Excelで入力規則（データバリデーション）を設定しているにもかかわらず、ユーザーが手動で貼り付けを行うなどして、規則に違反したデータが混入してしまうケースは少なくありません。
本記事では、VBAを使って「入力規則に違反したセルの数と位置を取得し、視覚的に把握する」方法をご紹介いたします。

想定する状況と目的

入力規則が設定された範囲に、手動入力や貼り付けによって不正なデータが混入している。
それらのセルを検出し、件数を表示したい。
違反セルを自動で目立たせて、後から修正しやすくしたい。

VBAコードの紹介

以下のコードを使用することで、不正データの件数と位置を自動的に検出し、対象のセルに赤色の塗りつぶしを適用できます。

Sub CheckInvalidEntries()

    Dim initialShapeCount As Long
    initialShapeCount = ActiveSheet.Shapes.Count
    
    ' 不正セルの強調表示処理（CircleInvalid などの自作プロシージャを呼び出す想定）
    ActiveSheet.HighlightInvalidCells
    
    ' 表示用メッセージ（図形が追加されている場合を想定）
    If initialShapeCount <> ActiveSheet.Shapes.Count Then
        DoEvents
        MsgBox "入力規則に違反しているデータの件数：" & _
               ActiveSheet.Shapes.Count - initialShapeCount & " 件です。", vbInformation
    End If
    
    ' 入力規則が設定されているセルを調べる
    Dim cell As Range
    On Error Resume Next ' 入力規則が無い場合のエラー回避
    For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
        If cell.Validation.Value = False Then
            cell.Interior.Color = RGB(255, 200, 200) ' 淡い赤で目立たせる
        End If
    Next
    On Error GoTo 0
    
End Sub