目次
別ファイルのセルを参照して色を付ける方法｜VBAで自動判定
エクセルを開いたときに他のエクセルのセルを参照して特定のセルを色付けする。
エクセルを多く管理していて、いちいち何枚もエクセルを開かないと作業が進まない事はよくある事ですが、マクロを組めばそういった事も自動化できます。
他エクセルの参照は普段使う事はありませんが、業務でエクセルで使う際は割とある機会です。とはいうもの他シートで作ればいいじゃん、そこでクリックして参照で問題は解決。
しかし、これがネットワーク上の他エクセルで他人も編集できるとなるとエクセルも他ファイルの方が何かと利便性はあるわけです。
この場合エクセルではなく、アクセスを使えば簡単にできますがアクセスは習得に時間がかかる上に業務用としての特性が強くソフトを持っていない人が多い事もあり
汎用性が低いため、エクセルでやりくりする方が効率が良いと言えます。
例題 Aファイルを開いた時にBファイルのセルを自動で判定し、Aファイルに色を付ける
今回はこのエクセルサンプルを使います。
エクセルを２ファイル用意します。
Aのエクセルはマクロを使いますので、拡張子は.xlsmで用意します。
Bのエクセルはマクロを使わないので普通のエクセルで構いません。
Bファイルの特定のセルの値を判別するマクロを作る
まずはマクロを作ります。メニューの「開発」タブから「マクロ」を押下します。
※）この作業はAファイルにて行ってください。
ボタンを押下すると「マクロ」という小窓が出てきます。
ここに自分でマクロ名を入力します。今回は「他エクセル参照」と入力しました。
入力すると「作成」ボタンが黒字になるのでそこを押下します。
Sub 他エクセル参照()
Dim wb参照元 As Workbook
Dim wb現在 As Workbook
Dim 値 As String
Set wb現在 = ThisWorkbook
'参照先ブックを開く（パスは自分の環境に合わせて）
Set wb参照元 = Workbooks.Open("E:\エクセルマクロ\他エクセル参照色付\B.xlsx")
'B.xlsx の Sheet1!B2 の値を取得
値 = wb参照元.Sheets("Sheet1").Range("B2").Value
' 条件によって現在のブックのセルに色をつける
With wb現在.Sheets("Sheet1").Range("B2")
If 値 = "OK" Then
.Interior.Color = RGB(255, 0, 0) ' 赤色
Else
.Interior.ColorIndex = xlNone
End If
End With
'参照元ブックを閉じる（保存しない）
wb参照元.Close SaveChanges:=False
End Sub
と書き込みです（書くのが面倒ならコードをコピペしてください）。
ここで注意があります。ピンクでマークした所はご自身の環境により書き直さなければいけません。
Set wb参照元 = Workbooks.Open("E:\エクセルマクロ\他エクセル参照色付\B.xlsx") の "E:\エクセルマクロ\他エクセル参照色付\B.xlsx"を ご自身のBファイルが置かれている場所に変更します。
（絶対パスで記述してください。 相対パスですとコードの記述方法がそもそも変わります。今回は絶対パスでのマクロになります。）
Bファイルが置かれている場所を確認するには、Bファイルを右クリックしてプルダウンから、パスのコピーを押せばクリップボードにコピーされます。
"E:\エクセルマクロ\他エクセル参照色付\B.xlsx" ←私のパソコンではこれがクリックボードにコピーされました。
Windowsのバージョンがちがう方で、パスのコピーという項目が無い人は、プロパティの 場所からも確認できます。
要はBファイルが格納されている場所を、絶対パスで指定します。
これでAファイルの判定マクロはOKです。これでBファイルのB2に「OK」といれれば Aファイルの「他エクセル参照」マクロを実行した時にAファイルのB2が赤色になります。
もちろんBファイルに「OK」ではなく違う文字が入っていた場合は無地になります。
このマクロは実行すると、マクロがBファイルを一回自動で開き 開いた瞬間に値を判定します。
Bファイルをいちいち勝手に開く行為には次の仕様があります。
もう少し便利に。
ここまでで一応マクロを実行すれば判定をしてくれて着色までしてくれるのですが、VBEマクロには欠点があります。
それはマクロを実行しないと判定してくれないという事です。
マクロはリアルタイム常時監視して判定をしてはくれません。使用上そのファイル（今回の場合は参照先のBファイルの事）を一回ひらかないと判定してくれません。
これはVBEの仕様なのです。常時完ぺきに監視して色付けを自動でするという事をしたいのなら他のソフト（アクセス等）を使うしかないという事なのです。
（ADOファンクションを使えば、閉じているBookからも値を取得できるが非常にプログラムが煩雑になります。）
それで疑似的にあるいはなるべく判定にミスが無いようにするためにはどうすればいいか。
いろいろ方法はありますが、とりあえずはAファイルを開いた時に、Bファイルをチェックしようというのが一番無難かと思います。
Aファイルを開いたときにマクロを自動発動する
Aファイルを開いたときに、Bファイルを参照するマクロを自動で発動する方法は、「開発」タブのマクロを開きます。（Alt + F11を押しても同様に開けます）
左側の「ThisWorkbook」をダブルクリック。右の白紙の窓に
Private Sub Workbook_Open()
他エクセル参照
End Sub
と記述します。
これを作っておけば、Aファイルを開いたときに 今回作った他参照マクロが1回自動で起動してくれてBファイルの中身を判定してくれます。
マクロを実行するボタンを作成する
さきほどのAファイルを開けば、Bファイルも勝手に開くコードを作っておけば大体OKなんですが、他の人がBファイルを編集している！なんていう事もあり常にリアルタイムで判定したい！
というビジネスマンもいるかもしれません。。。
ですのでマクロボタンを作ってしまえば、ボタンを押下すればその時に見てくれるという事も出来ます。
マクロをいちいちメニュー「開発」から「マクロ」をおして、「実行」ボタンを押すのは面倒です。
そこでボタンを表の中に作ってしまえばいつでもボタンを押すだけで この他ファイル参照のマクロを実行する事ができます。
マクロボタンの作り方
メニューの中の「挿入」、タブの中の「図形」、プルダウンメニューから 適当な形の物を選びます。
適当な所で図形を挿入します。
挿入した図形を右クリックして、プルダウンメニューからマクロ登録を押下。
その図形に当該マクロをセットできます。
次にわかりやすいように、図形に文字を記入します。
これでマクロボタン完成です。
開いたときに自動発動とマクロボタン 両方作っておけばなるべく取りこぼしなくリアルタイムで監視している状態になろうかと思います。
注意
エクセルは必ずマクロ有効ファイルの拡張子.xlsmで保存してください。
そうしないとマクロが保存されません。
まとめ
いかがだったでしょうか。
他のエクセルファイルの指定したセルを参照して、本体エクセルに着色するマクロでした。
このマクロのメリットは、いちいち他のエクセルを開かなくても確認できることに有利な点があります。
もちろん、一つの他エクセルだけではなく何個も何か所もセットできますので 利便性はかなりあがりますが、欠点としてはいちいち他エクセルが一度開いてしまう点です。
という事は、マシンパワーも当然浪費してしまうので 場合によってはフリーズやスタックを引き起こしてしまう場合もなくはありません。
完全にシステム化したい場合はアクセスを使うのが一番無難ではありますが １か所２か所の確認なら断然エクセルの方が楽なので紹介となりました。
