[[20170302162825]]　『数式の参照元の色表示を残す方法』（yyyymmmm）　ページの最後に飛ぶ

『数式の参照元の色表示を残す方法』（yyyymmmm）

計算式があるセルをクリックすると計算に使われているセル(参照元のセル)に
色が付きますが、一時的ですよね。(別のセルをクリックすると色が消える)
そこで、ある計算式に使われている参照元セルに自動的に色を付ける方法はありませんか。参照元セルも変えれば、自動的に色も変更先に移るようにしたいです。
「参照元のトレース」以外で、関数やVBAがあったらご教示下さい。

＜使用 Excel：unknown、使用 OS：Windows7 ＞

 SelectionChangeイベントで、Me.ClearArrows　を行ったうえで、ActiveCell　が　数式セルなら
 ActiveCell.ShowPrecedents とする方法もありますね。

 選択のたびにそうなるのが目障りなら、右クリックやダブルクリックで、同じことをするということも
 考えられますが。

（β） 2017/03/02(木) 17:05

ご回答ありがとうございます。ShowPrecedentsだと矢印表現になってしまうと思うのですが、色表現にすることはできませんか？お手数ですが、もしよろしければ、VBA初心者なので、具体的なプログラムを書いていただけると助かります。

<ほしい機能>
A1～A5に数値入力。B1に「=A1+A2+A3」と入力すると、A1、A2、A3が自動的に色(任意)が変わる。
また、B1を「＝A1+A4+A5」に変更すると、A2、A3の色が消え、A1、A4、A5が自動的に色(任意)が変わる。

以上、宜しくお願い致します。
（yyyymmmm） 2017/03/02(木) 17:37

 絶対に矢印が出たほうが見やすいと思うのですが、実際にやってみた意見ですか？

 ただ、まぁ、判断は、yyyymmmmさんの専管事項なので。

 シートモジュール（シートタブを右クリックしてコードの表示を選ぶとでてくるところ）に以下を貼り付けてください。

 セルに数式をいれると参照セル領域を黄色に塗ります。

 なお、コピペやオートフィルなどで複数セルに同時に入力した場合は、その先頭のセルのみ対象にします。

 なお、同一シート内の参照セルのみを対象にします。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim p As Range

    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    If Target(1).HasFormula Then
        For Each p In Target(1).Precedents
            p.Interior.Color = vbYellow
        Next
    End If
 End Sub

（β） 2017/03/02(木) 22:22

 色塗りバージョンをアップしましたが、だまされたと思って、上記を消して以下を貼り付け
 比べてみてください。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Me.ClearArrows
    If Target(1).HasFormula Then Target(1).ShowPrecedents
 End Sub

（β） 2017/03/02(木) 22:32

丁寧にご説明いただき、ありがとうございます。
両方、実行してみましたところ、上手く動作しました。
「矢印」ではなく、「色」としたのは、
上記の<ほしい機能>では、数式セルは1つですが、
本当は、数式があるセルが複数あり、そのセルごとに参照しているセルを
分かりやすく判別できるようにしたかったからです。
矢印だと、線が重なったりして、分かりづらくなると思ったので。
(すみません、説明不足でした。)
いただいたコードを元に、複数せるで色分けできるよう、
自分なりにいじってみようと思います。
ありがとうございます。

（yyyymmmm） 2017/03/03(金) 09:14

 >>複数せるで色分けできるよう、 自分なりにいじってみようと思います。

 はい、がんばってください。

 以下は老婆心。

 ・最初は、数式が入ったセルの参照先を視覚的にわかるようにしたいということだったので
 　SelectionChangeを使い、そのセルを選択した時に処理してはいかがかとコメントしました。
 　ところが、続く説明で、『数式を入力した時に・・・』という要件に変わっていたので
 　Changeイベントのコードをアップしました。
 　
 　で？？

 　最後には　『数式の入ったセルがたくさんあって・・・・』

 　Changeイベントを使うと、言葉通り　そのセルが【変更】されなければ動きません。
 　もう一度、視覚的に表現したかったら、そのセルが変わったとエクセルに認識させる操作が必要です。
 　（ダブルクリックしてエンターとか）

 　あるいは、そうではなく、複数の数式セルがそれぞれ、どのセルを参照しているかを、すべて表示する？
 　であれば、イベント処理ではなく、別の構成になります。

 　コード以前に、どういうタイミングで、どのようにしたら、どうなる　というシナリオを明確にして取り組まなければ
 　単なるコードの遊びになりますのでご注意を。

 それと

 >>複数せるで色分けできるよう、 自分なりにいじってみようと思います。

 この意味が =A1+B1 だった場合 A1 と B1 を色分けしたいということなのか、
 =A1+B1 というセルと　=SUM(G1:G100) というセル、それぞれの参照先を　前者は黄色、後者は水色、　といったように分けたいのか？

 それによっても、処理が異なります。
 前者であっても、後者であっても、じゃぁ、赤、青、黄色、・・・　どんな順番でどんな色をつけるのか？
 特に、後者の場合、シート上に数式セルが 1000個あった場合、その 1000個の色は、何色なのか、
 1000個まで用意してあったとして 1001個目の数式ができたら、それは何色なのか？

 そういった、コード以前のもろもろのことも明確に仕様として確定させてくださいね。

（β） 2017/03/03(金) 09:32

コメントありがとうございます。
今までVBAをやったことがなく、質問当初は、関数でできそうと思っていたので、仕様を詳しく書きませんでした。おおもとの数式セルは5つです。数式の内容は四則演算で、数式セル１「=A1+A2+A3」、数式セル２「=A4+A5+A6」・・・数式セル５「=A10+A20+A30」といった感じです。
したがって、参照元セルを５種類の色で判別したいのです。上でいうと、A1・A2・A3を赤、A4・A5・A6を青
・・・、黄色、茶色、A10・A20・A20を緑といった具合です。色はなんでもいいです。
タイミングとしましては、数式セルをダブルクリック時に参照元セルが色で括られるので、そのセルを任意のセルにドラッグして移動しエンターを押すと同時にセルの色も移り変わります。

また、最初いただいた色塗りのコードですが、このコードでは、参照元が数式の場合、さらにその参照元のセルまでも色塗りしてしまいました(B1セルが「=A1+A2+A3」でA3が「=C1-C2」の場合、本当はA1・A2・A3だけ色塗りしたいが、C1・C2も色塗りされてしまった。)。2次参照元までは、色塗りしないようにしたいです。
色々とアドバイスありがとうございました。

（yyyymmmm） 2017/03/03(金) 13:23

コメント返信：

[ 一覧(最新更新順) ]

[[20170302162825]] 『数式の参照元の色表示を残す方法』（yyyymmmm） ページの最後に飛ぶ

[[20170302162825]]　『数式の参照元の色表示を残す方法』（yyyymmmm）　ページの最後に飛ぶ