エクセル2003VBA ウインドウズXP 基準のセルをつくる

質問

エクセル2003VBA　ウインドウズXP　基準のセルをつくる
エクセルの”Worksheet_Change”を使って自動処理をさせようとしています。
1）セルA1に「基準セル」と入力してあるとします。
2）その下のセルA2に入力をしたとき、”Worksheet_Change”を使って
3）さらにその下のA3のセルを赤色で塗りつぶす
と言うVBAを組もうとしています。
2）3）の動作については、出来るのですが
1）の基準セルを作り方がわかりません。
自分のレベルですと、

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Excel.Range
Dim myRow As Long, myCol As Long
Set myRange = Range("A1")’※※※
myRow = myRange.Row
myCol = myRange.Column
If Target.Row - 1 = myRange.Row And Target.Column = myRange.Column Then
Range(Cells(myRow + 2, myCol), Cells(myRow + 2, myCol)).Interior.ColorIndex = 3
End If

End Sub

※※※の所をワークシートの「基準セル」と入力されている情報から取り込みたい
myRangeは配列にして、ワークシート上に「基準セル」と入力しておけば、基準セルになるように
したいと考えています。
どうすれば良いかご回答をお願いいたします。

回答No.3

kmetu · ベストアンサー

> 説明不足だったと思います。最終に望む形は、基準のセル位置に対して、複数のセルを
> 処理させようとしています。そのため、「基準のセル」の位置を取り込んで置く方法で
> 検討しております。

どちらにしても変更したセル(Target)と基準セルの位置を確認するんですよね。
でしたらTargetが基準セルから指定の位置に該当するかどうかを確認しても、Targetから見て指定の位置に基準セルかあるかどうか確認しても結果は同じ事です。

基準セルが複数あれば、前者なら、基準セルの位置をすべて保存しておかなければいけませんし、セルに変更があるたびに全ての基準セルとTargetの位置確認をしなければいけない(100個基準セルがあれば100回の確認が必要になります)など処理が重たくなります。

後者であれば、先に示した数行のコードですみますし、基準セルがいくつあろうと位置確認は一回で済みます。

とりあえず、複数の基準セルを作成して先に示したコードを実行してみてください。

お礼

ありがとうございます。
そうですね。処理の流れが整理できていませんでした。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Excel.Range

If Target.Offset(-1, 0).Value = "基準セル" Then
<<セルを処理する動作1>>
End If
If Target.Offset(-1, -1).Value = "基準セル" Then
<<セルを処理する動作2>>
End If
If Target.Offset(-1, -3).Value = "基準セル" Then
<<セルを処理する動作3>>
End If

とすれば、望む動作が創れる事に気付きました。
ありがとうございます。

End Su

回答No.4

kmetu

どうしても先に基準セルを配列で取り込んでおきたいのでしたら

> ThisWorkBookモジュールの"Workbook_Open()"イベントで「基準セル」を配列に取り込んでおいて、
> Sheetモジュールの”Worksheet_Change”イベントでその変数を使用していくには、どうするので
> しょうか？

標準モジュールで
Public test(256) As String
などとグローバル変数を指定してください。

回答No.2

kmetu

もしかして

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Excel.Range

If Target.Offset(-1, 0).Value = "基準セル" Then
Target.Offset(1, 0).Interior.ColorIndex = 3
End If

End Su

これでいいのではないですか。

補足

ありがとうございます。
説明不足だったと思います。最終に望む形は、基準のセル位置に対して、複数のセルを
処理させようとしています。そのため、「基準のセル」の位置を取り込んで置く方法で
検討しております。
たとえば、「基準セル」の下のセルに50と入力させた場合、50箇所のセルを自動で処理
させようとしています。そして、その処理させる位置も「基準セル」の位置に対して
相対的な位置を考えております。

回答No.1

kmetu

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Excel.Range

Set myRange = Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Find(What:="基準セル")
If myRange Is Nothing Then
MsgBox "基準セルがありません"
Exit Sub
End If
If Target.Address = myRange.Offset(1, 0).Address Then
myRange.Offset(2, 0).Interior.ColorIndex = 3
End If

End Sub

こういうことですか？

補足

ありがとうございます。
findで見つけておけばよいのですね。
あと、さらにお尋ねしたいのですが、「基準セル」はシートの使用状況によって増えて行く予定です。
それを、findとfindnextでmyRange()に配列で取り込んでおくとして、
その取り込みを"Workbook_Open()"イベントで取り込んでおこうと思うのですが、このとき、変数
宣言は何で行えばいいのでしょうか？
ThisWorkBookモジュールの"Workbook_Open()"イベントで「基準セル」を配列に取り込んでおいて、
Sheetモジュールの”Worksheet_Change”イベントでその変数を使用していくには、どうするので
しょうか？
かさねがさねすみません。よろしくお願いします。