エクセルVBAの記述法（Worksheet_Changeで）

質問

入力があればセルが黄色のなり、入力がなければ無色とするマクロです。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "" Then
Target.Interior.ColorIndex = 6
Else
Target.Interior.ColorIndex = xlNone
End If
End Sub

このように書くと、通常は正しく動きますが、ドラッグした場合や、複数セルを一度にクリアした場合、エラーになってしまいます。
ただしく作動させるにはどう直せばいいのでしょうか？

回答No.2

shishishishi · ベストアンサー

For Each ～Nextを使わずにやる方法が思い浮かばないので、たとえ列を選択された場合でも、範囲をUsedRangeと重なる部分のみに限定してみました。これならそんなに時間はかからないと思います。
ほかにももっといい方法があるとは思いますが・・・・。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set Target = Application.Intersect(Target, UsedRange)
If Target Is Nothing Then Exit Sub
For Each c In Target
With c
If .Value <> "" Then
.Interior.ColorIndex = 6
Else
.Interior.ColorIndex = xlNone
End If
End With
Next
End Sub

If Target Is Nothing Then Exit Subは、UsedRange外を削除したような場合のエラー防止のためです。

お礼

なるほど！
ありがとうございます。

回答No.1

hana-hana3

内部を以下のようにすると、正しく動作します。

Dim c As Range

For Each c In Target
With c
If .Value <> "" Then
.Interior.ColorIndex = 6
Else
.Interior.ColorIndex = xlNone
End If
End With
Next

お礼

さっそくありがとうございます。
なるほどFor Each ～Nextで一個ずつやるわけですね。

やってみましたところ、ちゃんと作動しました。ありがとうございました。ただ、列を選択してクリアした場合、一個ずつみるためだと思いますが、かなり時間がかかってしまいます。これはどうしようもないのでしょうか？