Excel2013,macroのcopy

質問

Excel2013でマクロ実行するのですが、最後のstepの「条件付き書式」のcopyが上手く行きません。どなたかご教授お願いします。

A～J列ｘ約4000行(1行～5行まではTytle行)のsheetです。
A列は日付で、過去からの日付となって居ます。
このsheetを見易くする為にA6行に
「条件付き書式」 =mod(month(A6),2)=0
を設定し、下記のmacro式でA6～J4000にCOPYするのですが、結果は上手く行きません。
※１：偶数月行のA列のみ正確に書式設定されるが、全列（A～J)にならない。
※２：奇数月のところどころ（列）に書式が設定される。
の不具合が生じます。

考えられる原因は何でしょうか？、ご教授願えませんでしょうか？。

与えたmacro

Range("A6").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(MONTH(A6),2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 11200714
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A6").Select
End Sub

回答No.5

WindFaller

こんばんは。
#3の回答者です。

>>Range("A6:J4000").FormatConditions.Delete　'←これを入れたらどうでしょう。
>この行はどの位置に入れるのでしょう？。

* コードにあるように、Sub TestForamtCopy() の次の行です。

>Sub TestForamtCopy() '←このタイトルの次の行。
>Range("A6:J4000").FormatConditions.Delete　'←これを入れたらどうでしょう。
>・
>・

>>考えられるのは、条件付き書式を重ねているということでしょうね
>A6（日付列cell）に条件付き書式を設定し、sheet全体（A6～J4000）にcopyしたいと考えているのです。これが”重ね”と言う事ならば、条件式はどの位置に書くのでしょうか？。

[重ねている]というのは、おかしな設定の条件付き書式を残したまま、私たちの条件付き書式を設定しても、いままでの条件付き書式が反応して、同じ結果になってしまうということです。それは、#4のtom04さんのご説明を読んでみてください。表現は違っても同じことを指摘しているはずです。

こんどは、両者とも、マクロを実行するだけで、前の設定がクリアされていますから、それで様子をみてください。この後の不具合があれば、それは、別問題として考えるということにして、とりあえず、2行目に挿入してから、試してみてください。

お礼

tom04さん
WindFallerさん

度々のご指導ありがとうございました。
上述の結果では満足できませんでしたので、ご指導内容を含めその後、何度も試行錯誤した結果下記内容にて完全に満足できる内容となりました。
ご指導に感謝いたします。

Range("A1:R4500").FormatConditions.Delete '←条件付き書式の消去★←ついでにDel範囲を広げました。
Range("A6:k4000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(MONTH($C6),2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 11200714
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range("A6:k4440").Select
Range("E3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[4430]C)" '←絶対値にしたいのですが取敢ずOK。
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:F3"), Type:=xlFillDefault
Range("E3:F3").Select
Range("A6").Select

END Sub

補足

tom04さん
WindFallerさん

度々のご指導ありがとうございます。
次の式（構文？）で実行してみました。

Range("A1:J4000").FormatConditions.Delete
With Range("A6:J4000")
.FormatConditions.Delete '←条件付き書式を一旦消去★
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A6<>"""",MOD(MONTH($A6),2)=0)"
.FormatConditions(1).Interior.Color = 11200714
End With

With Selection
.FormatConditions(1).StopIfTrue = False
.Copy
Range("A6:J4000").PasteSpecial (xlPasteFormats)
.Select
End With
Application.CutCopyMode = 0

End Sub

結果
全ての偶数月行の全ての列に、色塗り設定できました（奇数月行・列は無色）ありがとうございます。。
ただ、この構文のどこかで、”おかしい？？”のでしょうか？、
結果の値が変化（お化け）しています。

１．A列(日付=user定義の和暦文字=ge/mm)の値がC列に貼り付け、
２．B列(文字=全角2~6文字)の値なのでしょうか？D列に#######となり、
３．C列(西暦日付=yyyy/mm/dd)がE列にge/mmとなって、
４．D列(数値の計算式=D+E+F-G）がF列にge/mmとなって、
貼り付け(paste)られています。…どうしてなのでしょう？？？。
但し、cell内の値は元の値・式ですね。（######はカーソルを当てると”負であるか、大き過ぎる日付または時間”の説明が表示されます。中身は計算式なのですが‥‥）。

※これでは、色塗りは達成できても、値が化けてしまうと役に立ちません。
どこに間違いがあるのでしょうね？？？？。

回答No.4

tom04

No.1です。

＞意図していない行（奇数月）にも塗り色(11200714)が付けられ、また、偶数月行の色も全部行にはなりませんでした。

というコトですが・・・
実は当方もWindFallerさんと同じ考えですでにA6:J4000セルに条件付き書式が設定してあるのではないか？
と思いました。

↓のコードに変更してみてください。

Sub Sample2()
With Range("A6:J4000")
.FormatConditions.Delete '←条件付き書式を一旦消去★
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A6<>"""",MOD(MONTH($A6),2)=0)"
.FormatConditions(1).Interior.Color = 11200714
End With
End Sub

※　数式に「$」マークが入っている（列固定の複合参照）になっているコトに気を付けてください。

可能性としてはかなり低いとは思いますが・・・
これでもダメな場合はA列日付がシリアル値になっていない可能性があります。

こちらで検証した結果はちゃんとA列シリアル値が偶数月の場合
↓の画像のようにA列～J列まで色付けされます。m(_ _)m

お礼

tom04さん
WindFallerさん

度々のご指導ありがとうございました。
上述の結果では満足できませんでしたので、ご指導内容を含めその後、何度も試行錯誤した結果下記内容にて完全に満足できる内容となりました。
ご指導に感謝いたします。

Range("A1:R4500").FormatConditions.Delete '←条件付き書式の消去★←ついでにDel範囲を広げました。
Range("A6:k4000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(MONTH($C6),2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 11200714
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range("A6:k4440").Select
Range("E3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[4430]C)" '←絶対値にしたいのですが取敢ずOK。
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:F3"), Type:=xlFillDefault
Range("E3:F3").Select
Range("A6").Select

END Sub

補足

tom04さん
WindFallerさん

度々のご指導ありがとうございます。
次の式（構文？）で実行してみました。

Range("A1:J4000").FormatConditions.Delete
With Range("A6:J4000")
.FormatConditions.Delete '←条件付き書式を一旦消去★
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A6<>"""",MOD(MONTH($A6),2)=0)"
.FormatConditions(1).Interior.Color = 11200714
End With

With Selection
.FormatConditions(1).StopIfTrue = False
.Copy
Range("A6:J4000").PasteSpecial (xlPasteFormats)
.Select
End With
Application.CutCopyMode = 0

End Sub

結果
全ての偶数月行の全ての列に、色塗り設定できました（奇数月行・列は無色）ありがとうございます。。
ただ、この構文のどこかで、”おかしい？？”のでしょうか？、
結果の値が変化（お化け）しています。

１．A列(日付=user定義の和暦文字=ge/mm)の値がC列に貼り付け、
２．B列(文字=全角2~6文字)の値なのでしょうか？D列に#######となり、
３．C列(西暦日付=yyyy/mm/dd)がE列にge/mmとなって、
４．D列(数値の計算式=D+E+F-G）がF列にge/mmとなって、
貼り付け(paste)られています。…どうしてなのでしょう？？？。
但し、cell内の値は元の値・式ですね。（######はカーソルを当てると”負であるか、大き過ぎる日付または時間”の説明が表示されます。中身は計算式なのですが‥‥）。

※これでは、色塗りは達成できても、値が化けてしまうと役に立ちません。
どこに間違いがあるのでしょうね？？？？。

回答No.3

WindFaller

#2の回答者です。

>やりたい事は単純なのです。偶数月の行に色塗りをしたい(A～J)だけなのです。
6ヶ月ごとだと読み違えしていましたが、
[偶数月の行]ではなく、セルではありませんか。

>実行してみました。→結果はダメでした。
こんな単純なマクロで、ダメだということはありえませんし、こちらは問題ないのですから、考えられるのは、条件付き書式を重ねているということでしょうね。
つまり、
.FormatConditions.Add
しているのですから。

それなら、単に
Sub TestForamtCopy()
Range("A6:J4000").FormatConditions.Delete　'←これを入れたらどうでしょう。
・
・
・
'//
この場合は、範囲内の条件付き書式を全部削除してしまいます。

補足

WindFallerさん

追加のご指導ありがとうございます。
自分はmacroの事は全くの不勉強で、お恥ずかしながらmacro構文(単語=命令文)？の意味が解っていません"(-""-)"。
これまで「macroの記録」でmacroを作成して来ただけなのです。今回4000行にもなるsheetですのでカーソルの移動など正直不勉強なのです。

>Range("A6:J4000").FormatConditions.Delete　'←これを入れたらどうでしょう。
この行はどの位置に入れるのでしょう？。

>考えられるのは、条件付き書式を重ねているということでしょうね
A6（日付列cell）に条件付き書式を設定し、sheet全体（A6～J4000）にcopyしたいと考えているのです。これが”重ね”と言う事ならば、条件式はどの位置に書くのでしょうか？。

よろしくご指導のほど願います。

回答No.2

WindFaller

こんばんは。

こういう類は、記録マクロをあまりいじれないはずでしたが……
ご質問のマクロ自体は、Selection 自体が明確なら、問題ないような気がします。
ただ、私の場合、それを知らずに実行したので、Excelがハングしましたが。（^^;

念のために、セルに入れた日付の月数が、6で割れる数なら、色をつけるという趣旨ですね。
6月と12月ということになります。そうでないと、私の回答はボツです。ただ、条件付き書式として、CELL関数で、書式の選別をしないと、一般の整数まで、含まれてしまいます。

最初の
　Range("A6").Select
マクロの原則とは外れ、これが生きるはずです。
これを入れないと、条件付き書式を設定する場所と数式の関係が離れてしまいます。

もし、私の解釈が正しければ、おそらく、ホームの条件付き書式から見える数式とは違い、中身は、R1C1型の相対参照になっているような気がします。うまくいかない理由は、たぶんバグに近いのか、そういう仕様なのだと思います。ただ、コード全体が、VBA的ではありませんが、大真面目に書いています。

'//
Sub TestForamtCopy()
　Range("A6").Select
　With Selection
　　.FormatConditions.Add Type:=xlExpression, Formula1:= _
　　"=MOD(MONTH(A6),2)=0"
　　.FormatConditions(.FormatConditions.Count).SetFirstPriority
　End With
　With Selection.FormatConditions(1).Interior
　　.PatternColorIndex = xlAutomatic
　　.Color = 11200714
　　.TintAndShade = 0
　End With
　With Selection
　　.FormatConditions(1).StopIfTrue = False
　　.Copy
　　Range("A6:J4000").PasteSpecial (xlPasteFormats)
　　.Select
　End With
　Application.CutCopyMode = 0
End Sub
'//

補足

WindFallerさん

早速のご回答ありがとうございます。
実行してみました。→結果はダメでした。
tom04さんの結果と同じでした。

☆やりたい事は単純なのです。偶数月の行に色塗りをしたい(A～J)だけなのです。

回答No.1

tom04

こんばんは！

＞記のmacro式でA6～J4000にCOPYするのですが、結果は上手く行きません。
A6セルに条件付き書式を設定し、書式のコピーを行いたい！というコトだと思いますが。
数式そのものが「相対参照」になっているためだと思われます。

書式のコピーを行うのではなく、対象セル全体を範囲指定　→　数式による条件付き書式の設定！
という感じではどうでしょうか？

Sub Sample1()
With Range("A6:J4000")
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A6<>"""",MOD(MONTH($A6),2)=0)"
.FormatConditions(1).Interior.Color = 11200714
End With
End Sub

といった感じで・・・m(_ _)m

補足

tom04さん

早速のご回答ありがとうございます。
>書式のコピーを行うのではなく、対象セル全体を範囲指定　→　数式による条件付き書式の設定！という感じではどうでしょうか？
ありがとうございます。この考えでOKです。
Sample式で試してみました。→結果は残念賞でした。
やはり、意図していない行（奇数月）にも塗り色(11200714)が付けられ、また、偶数月行の色も全部行にはなりませんでした。

☆やりたい事は単純なのです。偶数月の行に色塗りをしたい(A～J)だけなのです。