プロが教える店舗&オフィスのセキュリティ対策術

タイトルが正しいかどうか疑問ですが。

シート[Sheet1]にて値を入力したアドレス(の行番号と列番号)を取得し、
その周囲のセルの罫線の色を赤(3)から灰色(15)に置換するコードを作っています。

Sheet1のコードには、
Private Sub Worksheet_Change(ByVal Target As Range)
 AAA Target
End Sub
とだけ書き、入力があったらプロシージャAAAへTargetを持って飛びます。

Sub AAA(ByVal Target As Range)
 Dim M_Row As Integer
 Dim M_Clm As Integer
 Dim Y As Range

 M_Row = Target.Row
 M_Clm = Target.Column

 For Each Y In Worksheets("Sheet1").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
  With Y
   If .Borders(xlEdgeTop).ColorIndex = 3 Then .Borders(xlEdgeTop).ColorIndex = 15
   If .Borders(xlEdgeLeft).ColorIndex = 3 Then .Borders(xlEdgeLeft).ColorIndex = 15
   If .Borders(xlEdgeBottom).ColorIndex = 3 Then .Borders(xlEdgeBottom).ColorIndex = 15
  End With
 Next
End Sub

ここまでは正常に動きます。
この後に、アクティブでないシート[Sheet2]の同じセル範囲にある罫線の色も同じように置換したいので、
上記コードに続けて、以下のように書きました。

Sub AAA(ByVal Target As Range)
 Dim M_Row As Integer
 Dim M_Clm As Integer
 Dim Y As Range

 M_Row = Target.Row
 M_Clm = Target.Column

 For Each Y In Worksheets("Sheet1").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
  With Y
   If .Borders(xlEdgeTop).ColorIndex = 3 Then .Borders(xlEdgeTop).ColorIndex = 15
   If .Borders(xlEdgeLeft).ColorIndex = 3 Then .Borders(xlEdgeLeft).ColorIndex = 15
   If .Borders(xlEdgeBottom).ColorIndex = 3 Then .Borders(xlEdgeBottom).ColorIndex = 15
  End With
 Next

 For Each Y In Worksheets("Sheet2").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
  With Y
   If .Borders(xlEdgeTop).ColorIndex = 3 Then .Borders(xlEdgeTop).ColorIndex = 15
   If .Borders(xlEdgeLeft).ColorIndex = 3 Then .Borders(xlEdgeLeft).ColorIndex = 15
   If .Borders(xlEdgeBottom).ColorIndex = 3 Then .Borders(xlEdgeBottom).ColorIndex = 15
  End With
 Next

End Sub

これだと、
 For Each Y In Worksheets("Sheet2").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
の部分で失敗します。
この1行前に、
 Sheets("Sheet2").Select
と入れてやると正常に動作するのですが、
シートを切り替えずにやりたいと思っています。
可能でしょうか?


以下のように、
実行後にSheet1に戻し、
それらを
Application.ScreenUpdating = False
Application.ScreenUpdating = True
で挟むことで、見た目はシートを切り替えずに実行できるのですが、
実際にこのコードを組み込んでいるシートはシート上にあるデータが多いためか(600行×100列程度)、
全く同じコードを実行しても一瞬画面がチラついてしまいます。
(新規Bookで同じコードを組み込んで、何行かに罫線を引いただけのシートだと全くチラつかなかったので、
シート上のデータが多いせいじゃないかと思いました)

Sub AAA(ByVal Target As Range)
 Dim M_Row As Integer
 Dim M_Clm As Integer
 Dim Y As Range
 M_Row = Target.Row
 M_Clm = Target.Column

 For Each Y In Worksheets("Sheet1").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
  With Y
   If .Borders(xlEdgeTop).ColorIndex = 3 Then .Borders(xlEdgeTop).ColorIndex = 15
   If .Borders(xlEdgeLeft).ColorIndex = 3 Then .Borders(xlEdgeLeft).ColorIndex = 15
   If .Borders(xlEdgeBottom).ColorIndex = 3 Then .Borders(xlEdgeBottom).ColorIndex = 15
  End With
 Next

 Application.ScreenUpdating = False
 Sheets("Sheet2").Select

 For Each Y In Worksheets("Sheet2").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))
  With Y
   If .Borders(xlEdgeTop).ColorIndex = 3 Then .Borders(xlEdgeTop).ColorIndex = 15
   If .Borders(xlEdgeLeft).ColorIndex = 3 Then .Borders(xlEdgeLeft).ColorIndex = 15
   If .Borders(xlEdgeBottom).ColorIndex = 3 Then .Borders(xlEdgeBottom).ColorIndex = 15
  End With
 Next

 Sheets("Sheet1").Select
 Application.ScreenUpdating = True

End Sub


よろしくお願いします。

A 回答 (1件)

> For Each Y In Worksheets("Sheet2").Range(Cells(M_Row - 2, M_Clm), Cells(M_Row + 1, M_Clm + 5))


のRangeはSheet2を参照しているのに、CellsはこのままではActiveSheetすなわちSheet1を参照することになるので、当然オブジェクト定義エラーになる。
ここは、長くなるが、
For Each Y In Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(M_Row - 2, M_Clm), Worksheets("Sheet2").Cells(M_Row + 1, M_Clm + 5))
としなければならない。
with
を使え。
    • good
    • 0
この回答へのお礼

回答ありがとうございます。

With Worksheets("Sheet2")
End With
で挟んで望んだような動作となり、解決しました。
Cellsの説明で「Objectの指定を省略したときにはアクティブシー
トが対象となります。」という文も発見しました。
ありがとうございました。

お礼日時:2008/02/15 10:26

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!