VBA 実行時エラー1004 について

質問

いつもお世話になります。

作表をしていて、項目に色をつけたいのですが
VBA 実行時エラー1004　Rangeメソッドは失敗しました。Globalオブジェクト
というエラーが
If Range(Cells(5, n - 4)).Interior.Color = RGB(252, 213, 180) Then
のところででます。
If Range("Z5").Interior.Color = RGB(252, 213, 180) Then
とすると、実行できます。

Sub カラー()
Dim n As Long '列番号取得
'最終列取得
n = Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox "最終列は" & n　　　'＝　今回は30です。

'セルの色を変える
If Range(Cells(5, n - 4)).Interior.Color = RGB(252, 213, 180) Then
Range(Cells(3, n - 3), Cells(5, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(39, n - 3), Cells(41, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(68, n - 3), Cells(70, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(104, n - 3), Cells(106, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(133, n - 3), Cells(135, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(169, n - 3), Cells(171, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(198, n - 3), Cells(200, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(234, n - 3), Cells(236, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(263, n - 3), Cells(265, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(299, n - 3), Cells(301, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(329, n - 3), Cells(331, n)).Interior.Color = RGB(230, 184, 183)
Range(Cells(365, n - 3), Cells(367, n)).Interior.Color = RGB(230, 184, 183)

Else
Range(Cells(3, n - 3), Cells(5, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(39, n - 3), Cells(41, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(68, n - 3), Cells(70, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(104, n - 3), Cells(106, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(133, n - 3), Cells(135, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(169, n - 3), Cells(171, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(198, n - 3), Cells(200, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(234, n - 3), Cells(236, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(263, n - 3), Cells(265, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(299, n - 3), Cells(301, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(329, n - 3), Cells(331, n)).Interior.Color = RGB(252, 213, 180)
Range(Cells(365, n - 3), Cells(367, n)).Interior.Color = RGB(252, 213, 180)

End If
End Sub

どこが間違っているのか教えていただけないでしょうか？
あと、スマートなコードの書き方もお願いします。

回答No.3

real beatin（@realbeatin） · ベストアンサー

こんにちは。

> どこが間違っているのか教えていただけないでしょうか？

まず、直接の対策として、
エラー箇所は、
If Cells(5, n - 4).Interior.Color = RGB(252, 213, 180) Then
のようにします。

Range(Cells(y1, x1), Cells(y2, x2))のように、
２つのセルを起終点として指定する使い方は正しいですが、
> Range(Cells(5, n - 4))
のように、Range()の内側の引数は、
Cells()を単独で指定することはできませんから、
Range()を無しにして
　Cells(5, n - 4)
のように指定してやれば良いです。

> あと、スマートなコードの書き方もお願いします。
' ' ///
Sub ReW9086872a()
Dim n As Long　'　列番号取得
Dim nColor As Long, nColor2 As Long
　　' ' 最終列取得
　　n = Cells(5, Columns.Count).End(xlToLeft).Column
　　MsgBox "最終列は" & n　　　'＝　今回は30です。

　　' ' 基準になる色を指定
　　nColor1 = RGB(252, 213, 180)
　　' ' 新たに塗り潰す色を指定
　　If Cells(5, n - 4).Interior.Color = nColor Then nColor = RGB(230, 184, 183)

　　' ' 新たに塗り潰すセル範囲を指定して、、、塗りつぶし。
　　Application.Intersect( _
　　　　Range("3:5,39:41,68:70,104:106,133:135,169:171,198:200,234:236,263:265,299:301,329:331,365:367"), _
　　　　Cells(n - 3).Resize(, 4).EntireColumn _
　　　　　　).Interior.Color = nColor
End Sub
' ' ///

「同じような記述を繰り返さない」
「同じ様な記述の中で変わる部分を変数にする」
というような事を心掛けると好い、という風に、
私も初学の頃に教わりました。

今回の場合は、塗り潰すセル範囲は固定ですから、
色の方を変数に収めれば、記述の繰り返しをなくせます。

塗り潰すセル範囲の捉え方だけ、内側から順に説明してみます。

１）
　　Range("3:5,39:41,68:70,104:106,133:135,169:171,198:200,234:236,263:265,299:301,329:331,365:367")
　　　　3:5行、39:41行、68:70行、、、365:367行、指定した複数ブロックの行を一纏めに参照します。
　　　　Range("3:5,39:41,68:70,104:106,133:135,169:171,198:200,234:236,263:265,299:301,329:331,365:367").Select
　　　　などを実行して試してみて下さい。
２）
　　Cells(n - 3).Resize(, 4).EntireColumn
　　　　Cells(n - 3)で、n-3列 1行め のセルを指します。→（n=30なら）Cells(1,27)＝Range("AA1")
　　　　.Resize(, 4)で、直前に指定したセル範囲の横方向のサイズを4列分に変更します。→（n=30なら）Range("AA1:AD1")
　　　　.EntireColumnで、直前に指定したセル範囲を列全体に拡張します。→（n=30なら）Range("AA:AD")
３）
　　Application.Intersect(range1, range2)
　　　　という書式で、「複数のセル範囲の共有セル範囲を表す Range オブジェクトを返します。」
　　　　１）２）に共通するセル範囲を返します。→（n=30なら）AA3:AD5,AA39:AD41,AA68:AD70, .... ,AA365:AD367
４）
　　.Interior.Color = nColor
　　　　３）までで指定したセル範囲の塗りつぶしを変数nColorに変更します。

不明、不足があれば補足欄にでも書いてみて下さい。
以上です。

お礼

realbeatinさん、いつも丁寧に教えていただきありがとうございます。

RangeとCellsの書き方の違い、よくわかりました。
コードをコピーして、適当に変更して作成していると、初歩的なことが抜けているんですね。
マクロが動くと、つい、理解できた気になっていたのですが。。。

今回のコードも今は解ったつもりなのですが、応用するときにまた悩むかもしれません。
その時はまた、よろしくお願いします。
ありがとうございました。

回答No.4

real beatin（@realbeatin）

No.3です。失礼しました。
誤って編集課程の方のマクロをあげてしまったようですので、
差し替えをお願いします。

' ' ///
Sub ReW9086872a()
Dim n As Long　'　列番号取得
Dim nColor As Long　'　塗りつぶし色
　　' ' 最終列取得
　　n = Cells(5, Columns.Count).End(xlToLeft).Column
　　MsgBox "最終列は" & n　　　'＝　今回は30です。

　　' ' 基準になる色を指定
　　nColor = RGB(252, 213, 180)
　　' ' 新たに塗りつぶす色を指定
　　If Cells(5, n - 4).Interior.Color = nColor Then nColor = RGB(230, 184, 183)

　　' ' 新たに塗りつぶすセル範囲を指定して、、、塗りつぶし。
　　Application.Intersect( _
　　　　Range("3:5,39:41,68:70,104:106,133:135,169:171,198:200,234:236,263:265,299:301,329:331,365:367"), _
　　　　Cells(n - 3).Resize(, 4).EntireColumn _
　　　　　　).Interior.Color = nColor
End Sub
' ' ///

回答No.2

ushi2015

こんにちは

Sub test()
Dim n As Long
Dim i As Long
Dim c As Long
'最終列取得
n = Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox "最終列は" & n '＝　今回は30です。
If Cells(5, n - 4).Interior.Color = RGB(252, 213, 180) Then
c = RGB(230, 184, 183)
Else
c = RGB(252, 213, 180)
End If
With Cells(3, n - 3).Resize(3, 4)
For i = 0 To 4
.Offset(65 * i).Interior.Color = c
.Offset(36).Offset(65 * i).Interior.Color = c
Next
i = 5
.Offset(65 * i + 1).Interior.Color = c
.Offset(36).Offset(65 * i + 1).Interior.Color = c
End With
End Sub

こんな感じも。

お礼

ushi2015さま、ありがとうございます。

大変勉強になりました。

これからもよろしくお願いいたします。

回答No.1

dogs_cats

一例です。
Sub カラー()
Dim n, i As Long '列番号取得
Dim ColNo As Variant
Dim myRGB As String

ColNo = Array(3, 39, 68, 104, 133, 169, 198, 234, 263, 299, 329, 365)

'最終列取得
n = Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox "最終列は" & n '＝　今回は30です。

'セルの色指定
If Cells(5, n - 4).Interior.Color = RGB(252, 213, 180) Then
myRGB = RGB(230, 184, 183)
Else
myRGB = RGB(252, 213, 180)
End If

'セルの色変更
For i = 0 To UBound(ColNo)
Range(Cells(ColNo(i), n - 3), Cells(ColNo(i) + 2, n)).Interior.Color = myRGB
Next i

End Sub

最大列からセル色範囲を指定していますが、セル色の列が毎回変動するなら前回指定の色を塗りつぶし無しにしなくて良いのか、気になるところです。

If Range(Cells(5, n - 4)).Interior.Color = RGB(252, 213, 180) Then
セル１つだけを参照していますので、rangeは不用で、

If Cells(5, n - 4).Interior.Color = RGB(252, 213, 180) Then

colnoという一次配列に行番号を格納しています。
arrayを使用していますので要素番号は0から開始されます。
UBound(ColNo)で変数colnoの一次配列要素数を取得しています。要素番号は0から開始されるので11という数値を取得します。（12ケの数値でfor~nextは実施します)

array
http://www.officepro.jp/excelvba/array/index6.html

UBound
http://officetanaka.net/excel/vba/function/UBound.htm

お礼

dogs_cats さま、ありがとうございました。

この表は、毎月2回作成するのですが、1回につき、4列作成するだけなので比較できるように、前回の右に追加するようにしました。
作成する度に、項目の色を交互にしていくため、塗りつぶし無しにはしていません。（ちょっとカラフルすぎかもしれませんね）

コードはとても勉強になりました。
配列はまったく理解できていないので、これから勉強したいとおもいます。

Dim n, i As Long
これも、勉強になりました。
ありがとうございました。