入力チェックを一括で行い、エラーのセルに色を付ける

どんな場面で使うか

複数の人が入力する一覧表（顧客登録、経費精算、受注入力など）を、基幹システムに取り込む前にチェックする作業です。取り込み時にエラーになってから原因を探すより、入力した人が自分でチェックを実行して、その場で直せる方が手戻りが少なくなります。

Excel の「データの入力規則」でも一部は防げますが、貼り付けると規則が効かない、複数の項目にまたがる条件を書きにくい、といった弱点があります。マクロで最後にまとめてチェックする仕組みを用意しておくと安心です。

チェックする内容

この記事では、次の4種類のチェックを行います。

• 必須：A列（顧客コード）、B列（顧客名）が空でない
• 桁数：A列は6桁の数字
• 日付：C列（契約日）が日付として正しい
• 数値：D列（金額）が0以上の数値

コード

エラーのあったセルは赤く塗り、セルのコメントに理由を書きます。実行のたびに前回の色とコメントを消してからチェックし直します。

Sub CheckInput()
    Dim ws As Worksheet, lastRow As Long, i As Long, errCount As Long
    Set ws = ThisWorkbook.Worksheets("入力")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If ws.Cells(ws.Rows.Count, "B").End(xlUp).Row > lastRow Then lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    With ws.Range("A2:D" & Application.Max(lastRow, 2))
        .Interior.ColorIndex = xlNone
        .ClearComments
    End With

    For i = 2 To lastRow
        If Len(Trim(ws.Cells(i, "A").Value)) = 0 Then
            MarkError ws.Cells(i, "A"), "顧客コードが未入力です", errCount
        ElseIf Not (Len(ws.Cells(i, "A").Text) = 6 And IsNumeric(ws.Cells(i, "A").Value)) Then
            MarkError ws.Cells(i, "A"), "顧客コードは6桁の数字で入力してください", errCount
        End If
        If Len(Trim(ws.Cells(i, "B").Value)) = 0 Then MarkError ws.Cells(i, "B"), "顧客名が未入力です", errCount
        If Not IsDate(ws.Cells(i, "C").Value) Then MarkError ws.Cells(i, "C"), "契約日が日付になっていません", errCount
        If Not IsNumeric(ws.Cells(i, "D").Value) Or Len(ws.Cells(i, "D").Value) = 0 Then
            MarkError ws.Cells(i, "D"), "金額は数値で入力してください", errCount
        ElseIf ws.Cells(i, "D").Value < 0 Then
            MarkError ws.Cells(i, "D"), "金額がマイナスです", errCount
        End If
    Next i

    If errCount = 0 Then
        MsgBox "エラーはありません"
    Else
        MsgBox errCount & " 件のエラーがあります。赤いセルのコメントを確認してください", vbExclamation
    End If
End Sub

Private Sub MarkError(ByVal c As Range, ByVal msg As String, ByRef cnt As Long)
    c.Interior.Color = RGB(255, 199, 206)
    If c.Comment Is Nothing Then c.AddComment msg Else c.Comment.Text c.Comment.Text & vbLf & msg
    cnt = cnt + 1
End Sub

コードのポイント

最終行は A列と B列の両方で調べ、大きい方を使っています。顧客コードの入力を忘れた最後の行が、チェック対象から漏れるのを防ぐためです。

6桁のチェックでは、セルの Value ではなく Text（表示されている文字）を使っています。「001234」を数値として入力すると Value は 1234 になってしまうため、表示上の桁数で判定しています。顧客コードの列は、あらかじめ表示形式を「文字列」にしておくのがおすすめです。

運用のコツ

チェック用のボタンをシートに置き、「入力が終わったらボタンを押して赤いセルがなくなるまで直す」というルールにすると、提出前のチェックが定着します。チェックの条件は業務によって違うので、どんな入力ミスが多いかを振り返り、少しずつ条件を追加していくと効果的です。

ほかの記事