エクセルで特定の背景色を持つセル内の数値を検査したいのですが

質問

エクセルでブック内の全シートに対して、B列からE列に数値が設定されているブックがあり、
A列にデータのナンバーが振られており、最後のデータの一つ下にはデータの終わりを告げる、EOFという文字列が入っています。
その数値群の中で、特定の背景色を持つセルには必ず0が入っているはずなのですが、手入力のミスでいくつかのセルに他の数値が入っていました。
手作業ですとミスが発生してしまうので、エクセルの機能かマクロで以下の条件で検索をかけ、NGデータがあるかどうかの検査をしたいと考えていますが、可能でしょうか。

条件１：セルの背景色=灰色(エクセルの背景色選択画面で一番薄い灰色です)
条件２：セルの数値=0以外の数値や文字列

データの構造例
　　　　A列　/　　B列　 　/　　C列　 　/　　D列　 　/　　E列　 　/
1行目　1　/10(背景白)/0(背景灰)/0(背景灰)/10(背景青)/
2行目　2　/10(背景白)/0(背景灰)/2(背景灰)/10(背景青)/
3行目　3　/10(背景灰)/0(背景赤)/0(背景赤)/10(背景青)/
中略
145行目　145　/55(背景白)/0(背景白)/0(背景灰)/0(背景青)/
EOF

上記の場合だとセル2Dに誤った数値が入っていることを告知するようなものを考えています。

シート総数は140シートで、エラーが見つかった時点で検索を終わっても問題ありません。（最後にたどり着くまで修正、チェックを繰り返しますので）

回答No.2

zap35 · ベストアンサー

＞NGデータがあるかどうかの検査をしたいと考えていますが、可能でしょうか
おそらく可能です。でも質問文を拝見して「何をどうすれば良いのか」よく理解できません。だからチェックするマクロを書いても「私が思っているのと少し違う」と言われかねません（これをやられると回答側はへこみます）

だから「指定したセルの背景色インデックス番号を取得する」汎用的なをユーザ定義関数（＝マクロ）を回答します。これを使えば関数で処理ができますから応用が利くと思います

以下のマクロをAlt＋F11でVBE画面を開き、左側のVBA Projectのシート名右クリック→挿入→標準モジュールで開く画面に貼り付けてください。
Function ColorIndex(ByVal trg As Range) As Integer
　　If trg.Interior.ColorIndex = xlNone Then
　　　　ColorIndex = 0
　　Else
　　　　ColorIndex = trg.Interior.ColorIndex
　　End If
End Function

VBE画面は閉じてシート画面に戻り、適当なセルに
　=ColorIndex(A1）
と式を入力するとA1セルの背景色のインデックス番号を返します。

関数なら
　=IF(AND(A1<>0,ColorIndex(A1)=37),"Error","OK")
のようにチェックができますよ（インデックス番号は適当です）

ただし背景色を変える動作は関数の値を変える契機になりません。（セルの値が変更されないと関数は自動計算されません）
ですから背景色だけを変更してチェックし直すにはAlt+Ctrl＋F9で「強制再計算」してやる必要がありますのでご注意ください

お礼

エクセルマクロは記録マクロを元に弄くったりとかはよくやるのですが、
自分で関数を定義できるのですね！
初めて知りました、有難う御座います。
教えていただいたものを元に作成してみようと思います。
素早いご回答有難う御座いました。

回答No.1

imogasi

VBAでなら可能な課題です。VBAは経験ありますか。質問者のスキルと少しの勉強で出来るとか出来ないかどうかの結論が出ます。関数などでは出来ません。
シート数とセル数がそんなに多くなければ、
条件付き書式で、新たに０のセルに目立つセルの色を設定し、目視でチェックすることも、VBAプログラム作成で苦労するレベルなら、結局早く完了して、能率的かと思うが。
コードのヒントだけ
標準モジュールに
Sub test01()
Dim sh As Worksheet
For Each sh In Worksheets
MsgBox sh.Name
d = sh.Range("A65536").End(xlUp).Row
MsgBox d
For Each cl In sh.Range("A1:E" & d - 1)
MsgBox cl
' 処理
Next
Next
End Sub
ーー
上記で全シートとA1:Ex（ｘはEOFの前行まで）を捉えていることを確認し、処理のところへ、値０とセルのパター色の対応のチェックのコードを入れること。
その際Msgboxの各行は削除する。
誤りが見つかれば何らかの方法で表示する。

お礼

質問の際にこちらのスキルを書くべきでした。
残念ながらデータ数が膨大ですので、目視では厳しかったです。
素早いご回答とご指摘、有難う御座いました。