とてつまない大きい表を扱いやすくする～動く条件付き書式～

【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】

とてつもなく大きい表を使っているときに迷子になることがありませんか？

「今、どの行とどの列のセルを扱っているんだろう・・・」

しまいには、ミスが怖いので表を印刷して「定規」を当てたりしたことはありませんか？

　今回は迷子対策として、「動く条件付き書式」の事例を紹介します

　この動く条件付き書式では、カーソルを置いた位置の行列の色が変わるので、「迷子対策」にはバッチリです！

紹介する事例は２つあります！

1つ目の事例は、CELL関数とROW関数、そしてCOLUMN関数を使用して条件付き書式を設定します

こちらは、手動で条件付き書式を設定する内容になっています

　＊イベントプロシージャ―に１行コードを書く必要があります

この方法の場合には、重たいファイルの場合だと「条件付き書式」の動きがかなり重たくなります　

　２つ目の内容（動画あり）は、イベントプロシージャ―を使用して、カーソルが動く都度「条件付き書式」をマクロで自動設定します

　こちらは条件付き書式の範囲を面で指定するわけではなく、「行列」双方向の2本の線で範囲を設定しますので、あまり動きを重くしないですみます

　後、２つ目の方法の場合には色をシート上で設定できるようにするなどの、実践的に使えるようにする為の工夫を複数行っています

①条件付き書式を全て手動で設定

まずは、最初に条件付き書式を設定する範囲を手動で指定します

その後、条件付き書式の設定画面を開き、ルールの種類の選択を「数式を使用して、書式設定する設定を決定」にします

「次の数式を満たす場合に値を書式設定」には、画像のような数式を設定します

数式：=OR(CELL(“ROW”)=ROW(),CELL(“COL”)=COLUMN())

これで条件付き書式の設定は終了です

実はもう一つ、仕掛けが必要です

イベントプロシージャ―を使用し、セルの選択が変更になった場合に「更新処理」を行う必要があります

　更新処理を設定するには、まずは、上の画像のように該当のシートで右クリックをして「コード表示」を選択します

　次に開いたVBE（Visual Basic Editor）画面では、下の画像の黄色の印の箇所がそれぞれ「Worksheet」「SelectionCange」になっていることを確認します

　確認ができたら、プロシージャ―には1行だけコード＊を入れれば、条件付き書式の更新処理が設定されます

*Application.ScreenUpdating = True

②都度、条件付き書式の範囲をマクロで設定

　こちらは、前述のようにこの「動く条件付き書式」を実践的に使えるように様々な工夫を行っています

各種設定シートというシートを設けてあり、様々な設定が行えます

工夫一覧

１．シート上での色設定

各種設定シートにて、色を指定すれば、条件付き書式で設定する色を変更できます

２．開始行や開始列の設定

条件付き書式の設定を行う開始行と開始列はシート上で調整できます

上の画像では開始行が6行目、開始列が1列目ですので下の画像のように色が変わります

３．停止・再開処理他

この動く条件付き書式は「停止」したい時があると思います

この際は、停止ボタンを押せば停止ができる仕掛けになっています

停止処理を行った場合には、停止前に設定された色が残ってしまっています

ですので、上の画像の消去ボタンを押せば「設定残り」の色は消去できます

コード上の工夫

こちらは、ごく簡単なポイントだけを解説させて頂きます

この事例は、前述のように条件付き書式の範囲が拡大したり縮小する場合も想定しつつ、処理が重たくならないようにします

こちらの場合は、色の付け方が、1番目の方法と変えてあります

カーソルがある位置が、色のつく範囲の最終行と最終列になります（1番目の方法も可能ではありますが、今回は極力色設定する範囲を小さくするという趣旨でこの方法にしました）

ポイント１ ⇒面ではなく、行方向1行、列方向1列で範囲設定

カーソルを移動させた際は、イベントプロシージャ―にてカーソル位置を取得します（Targetを引数にして取得）

カーソル位置を取得したら、２つ範囲を設定します

１つ目は行方向です。範囲の開始列は1列目などを固定で指定して範囲設定します

２つ目は列方向です。範囲の開始行は1行目などを固定で指定して範囲設定します

ポイント２ ⇒設定済みの条件付き書式は事前にクリアしておく

重複して条件付き書式が設定されないように、設定済みの条件付き書式はクリアしておきます

ポイント３ ⇒マクロ処理の途中でイベントプロシージャ―は止めておきます

１つの範囲に条件付き書式を設定した際に、更にイベントが起動してしまいますので、次の1行のコードでイベントを中止しておきます（後で再開が必要です）

Application.EnableEvents = False

＜まとめ＞

　今回はとてつもなく大きい表を扱う際、迷子にならないようにカーソルがある行と列の色を変える方法を紹介しました

但し、

特に2番目の方法などが当てはまるのですが、実際に業務で活用する場合は、さらに一工夫が必要です

　例えば、2番目の方法は設定済みの条件付き書式をクリアしてしまいますので、「迷子対策」以外の条件付き書式の設定が消えてしまうケースも想定されます

　ですので、別途「迷子対策以外」の条件付き書式も自動設定する必要があります　

それでは、最後まで長文にお付き合い頂きありがとうございました！

　今回紹介した仕組みは、実務では更に工夫の余地があると思いますが、エクセルVBAならではの便利さが生み出せると思います！

　少しでも、大きい表を扱う際の「イライラ」を消すことに貢献できたら幸いです

　もしも、この仕組みに興味を持たれた方がいらっしゃいましたら、ぜひメールを下さい