【関数orVBA】カーソルのある行を黄色にし、A列の値を別シートに表示できますか？

※説明が難しくて長文失礼します。
1つのExcelブックの中に、次の2つのシートがあります。
・一覧表
・個人明細

「一覧表」シートには、A列、B列・・・に氏名、部署、担当案件名、予算金額・・・
という項目名で数百レコード入っています。
「個人明細」シートは、「一覧表」の1名分(1レコード)の情報が、
A4用紙での印刷用にレイアウトされたシートになっています。
具体的には、「個人明細」シート内の「氏名」欄からvlookupで、
「一覧表」シートから突合して表示しています。
現在は、「個人明細」シートの「氏名」セルの入力規則-リストの対処を
「一覧表」シートのA列に設定しているので、
プルダウンから必要な人を選択をすれば、その人の詳細情報が表示されます。

これを次のような仕様に出来ないでしょうか？

新しいウィンドウを開いて、「一覧表」と「個人明細」を並べて表示にして、
「一覧表」のカーソルの行と「個人明細」に表示する内容を連動させたいと思います。
その為に、
「一覧表」にある現在のカーソルのある行を取得できないでしょうか？
例えば67行目にカーソルがあったとしたら、
A67セル内の値を、「個人明細」のC2セルに表示させたいです。
更に「一覧表」の67行目はセル色を黄色にしてカーソルがある位置を一目瞭然にしたいです。
(条件付き書式でもできるかもしれませんが動作を軽くしたいです)

つまり、「一覧表」のセルをカーソルキー「↓」で移動していくだけで、
「個人明細」の表示内容が変わっていくようにしたいです。
関数またはVBAで実現できますでしょうか？
詳しい方、よろしくお願いいたします。

No.1 回答者： mygoonickname 回答日時： 2021/12/28 03:04

こんばんは。

＞条件付き書式
→こちらでは、希望の事はできないので、やるならVBAになるかと。

カーソルでやるなら、イベントの、SelectionChangeを使う事になるかと。
https://excel-ubara.com/excelvba1/EXCELVBA425.html


>例えば67行目にカーソルがあったとしたら、
A67セル内の値を、「個人明細」のC2セルに表示させたいです。

Worksheets("個人明細").Range("C2").Value = Cells(ActiveCell.Row,1).Value

上記を組み合わせれば、できるのでは？と思います。
先ずはチャレンジされてみて、不明点があったら、コードをアップして、
やりたい結果、現状の結果などで、再度質問をされたら？と思います。

この回答へのお礼

ありがとうございます。
SelectionChange ですね。ヒントを元に調べてみます。

お礼日時：2021/12/28 22:00

No.2 回答者： chonami 回答日時： 2021/12/28 12:19

私だったら個人明細シートの氏名を入れるセル（C2？）にMATCH関数で一覧表シートのA列を指定し行を入れるところを別セルで参照します。

行を指定する別セルにスピンボタンを配置すればそこを上下するだけでデータが変更になっていくかと思います。

この回答へのお礼

ありがとうございます。
MATCH関数をどこに入れてどう参照するかがピンときませんでした。
ちょっと時間をかけてテストしたいと思います。
スピンボタンは、よく年齢など「01」を上下の矢印で増減させるボタンですよね？
理想の操作性としては、1行ずつ下げるだけではなく、離れた場所をクリックしても良いように対応したいです。
(No.3の回答がとても理想に近かったです。)

お礼日時：2021/12/28 22:04

No.3 回答者： fujillin 回答日時： 2021/12/28 16:01

こんにちは

関数ではカーソル位置（＝選択セル）の位置を取得することはできないので、VBAでなければ難しいと思います。

＞条件付き書式でもできるかもしれませんが動作を軽くしたいです
カーソル位置を監視することになるので、「軽く」と言ってもそれなりの負荷がかかります。
また、VBA自体が速度が遅いので、エクセルの組み込み機能に比較すれば遅いことにもなります。
とはいえ、ご提示程度の内容であれば、操作にはさほど影響が出るようなことは無いでしょう。

※ 個人明細シート内に複雑な関数式が設定されていて、C2セルを参照しているような場合は、そちらの影響で重たくなる可能性があります。


下のサンプルでは、勝手に以下の仮定をしています。
・一覧表で色を付ける対象は10列まで（A：J）とする
・A:J列の基本色は色なしとする
　（以前の色を消すため、元々色を付けたセルがあっても消されるので）
・感知する対象はA列のみとする
　（その他の列を選択しても、行の色は変わりません）

一覧表のシートの、シートモジュールに以下のコードをコピペしてみてください。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const TSheet = "個人明細"

Range("A:J").Interior.Color = xlNone
If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
Target.Resize(1, 10).Interior.Color = vbYellow
Worksheets(TSheet).Range("C2").Value = Target.Value
End Sub

この回答へのお礼

ありがとうございます。
いきなり理想通りで完璧な動作をしました！

自分なりの理解では
> If Target.Count > 1 Or Target.Column <> 1
この数値を3にすれば対象列をC列に出来ることが分かりました。

> Range("A:J")
> Target.Resize(1, 10)
黄色で塗る範囲をBR列まで広げたければ、
("A:BR")、(1, 70)にすれば出来る事が分かりました。

申し訳ありません、追加の質問です。
１．1行目～5行目はレコードの範囲外です。1行目～5行目を塗りつぶし範囲から外す事は出来ますか？
２．また最終行以降も反応しないように出来ます？
３．感知する対象を3(C列)にした場合、C列より右しか塗られませんでしたがどんな場合でもA列から塗るように出来ますか？
４．カーソル位置がどの列にあったとしても関知するように出来ないでしょうか？

お礼日時：2021/12/28 22:48

No.4 ベストアンサー 回答者： fujillin 回答日時： 2021/12/29 10:42

No3です

＞申し訳ありません、追加の質問です。
要望ではっきりしない部分がありますので、ある程度設定できる形にしておきました。

※ 定数にしてある部分を適当に変更してください。
※「最終行」はC列の最終行を用いています。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MaxRow As Long, rw As Long

Const TSheet = "個人明細" '←転記先シート名
Const RColumn = 70 '←感知対象列範囲（最大列番号）
Const TColumn = 3 '←転記元セルの列番号

MaxRow = Cells(Rows.Count, 3).End(xlUp).Row
If MaxRow < 5 Then MaxRow = 6
Range("A6").Resize(Rows.Count - 5, RColumn).Interior.Color = xlNone
rw = Target.Row
If Target.Count > 1 Or rw < 6 Or rw > MaxRow Or _
Target.Column > RColumn Then Exit Sub
Cells(rw, 1).Resize(, RColumn).Interior.Color = vbYellow
Worksheets(TSheet).Range("C2").Value = Cells(rw, TColumn).Value
End Sub

この回答へのお礼

ありがとうございます。
はっきりしない部分があったとのこと、すみません。。。
しかしやりたかった追加部分は全てクリアされて完璧に満たされてました。
変数化されていてすごく便利です。
更にカスタマイズしたい箇所があったらソースを見て勉強します。
大変助かりました。
ありがとうございました！

お礼日時：2021/12/29 17:56