Intersect　で範囲に変数を使いたい

お世話になっております。

行が増える可能性のあるエクセルの更新をVBAでやろうとしております。

セルの値が変更された時に動作させるたいのでググって
https://www.moug.net/tech/exvba/0050131.html
記載のように

VBA
1Private Sub Worksheet_Change(ByVal Target As Range)
2    If Intersect(Target, Range("C5:AY27")) Is Nothing Then
3        Exit Sub
4    Else
5        SetColorSampleEx2
6'        MsgBox "セルの値が変更されました"
7    End If
8End Sub
9
10Sub SetColorSampleEx2()
11    Dim i, j, k As Long
12    For j = 0 To 10
13        For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
14            Cells(i, 7 + j * 5).Interior.Color _
15                = RGB(Cells(i, 3 + j * 5).Value, Cells(i, 4 + j * 5).Value, Cells(i, 5 + j * 5).Value)
16        Next
17    Next
18End Sub

と実装していたのですが

行を増やした際にも正常に動作させて欲しいとの要望がありました……
つまり

    If Intersect(Target, Range("C5:AY27")) Is Nothing Then

の　　　　　　　　　　　　　　　　　　↑27が増えても
自動で使えるようにしたいのです……

こういった場合どのような実装方法があるのかお伺いしてよろしいでしょうか？

”intersect vba 使い方　変数”などでググっても使い方が見つからなかったのです……

お忙しい所大変申し訳ないのですが何か情報をお持ちな方はコメント頂けますと嬉しく思います。

行動規範の内容に同意します

回答1件

ベストアンサー

Range("C5:AY27") の範囲は具体的にどのようなデータが入っているのでしょうか。

例えば4行目に項目名が入っていて5行目からデータが入力されている表形式ならば、
CurrentRegion で表範囲を取得できますので、それを使えばいいでしょう。

vba
1If Intersect(Target, Range("C5").CurrentRegion) Is Nothing Then

先頭行が項目名になっているとそれも含まれますので、下記のようにするとデータ行のみ対象にできまず。

vba
1    Dim cr As range
2    Set cr = Range("C5").CurrentRegion
3    Set cr = cr.Offset(1).Resize(cr.Rows.Count-1)
4    If Intersect(Target, cr) Is Nothing Then

おまけのアドバイス

表形式データなら、テーブル化にしておいて名前を付けておくと、
Range("テーブル名") でデータ範囲を取得できます。
データ入力して行が増えても自動でテーブル範囲も拡張します。

vba
1If Intersect(Target, Range("テーブル名") Is Nothing Then

テーブルにするといろいろメリットがあります。

【Excel】表を「テーブル」にするだけで作業効率がアップ

投稿2021/10/28 04:15

編集2021/10/28 04:26

hatena19

総合スコア33795

ma2hiro

2021/10/28 05:03

hatena19さまコメントありがとうございます。＞Range("C5:AY27") の範囲は具体的にどのようなデータが入っているのでしょうか。カラーサンプルとそのRGBの値です。 ``` Dim cr As range Set cr = Range("C5").CurrentRegion Set cr = cr.Offset(1).Resize(cr.Rows.Count-1) If Intersect(Target, cr) Is Nothing Then ``` 範囲が可変で増えても上記　Intersect　が動作するようにしたかったのです…… TOPのヤツでは27行目までしか動作しない…… 説明すると ================================ 　A　　　B　　C　D　E　F　　G　H　I　J　K　　L……　AZ 1　説明文：　： 4　説明文 5 1　説明文　R　G　B　文　色　R　G　B　文　色…… 6 2　説明文　R　G　B　文　色　R　G　B　文　色…… 7 3　説明文　R　G　B　文　色　R　G　B　文　色…… 8 4　説明文　R　G　B　文　色　R　G　B　文　色…… ================================ CDEの色をGに入れ　HIJの色をLに入れ……　AV,AW,AXの色をAZに入れるマクロがありデータは27行目までは確実にあるのですが 27行目から増やしても動作出来るようにしたいのです…… 度々申し訳ないのですがご助力頂けますと嬉しく思います。