質問
2021年2月20日土曜日 4:04
カレンダー上での仕様です
下記のような条件でセルの色を変更したいのですが
条件付き書式の適用になっているセルが変更されません
条件付き書式はN1～N2の日間を塗りつぶしています
どのように変更すればよいのか教えてください
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target.Interior
Cancel = True
If .ColorIndex = xlNone Then
.ColorIndex = 7
Else
.ColorIndex = xlNone
End If
End With
Cancel = True
End Sub
すべての返信 (12)
2021年2月20日土曜日 12:43 ✅回答済み
まず行った内容をお伝えしますのでもう一度教えていただきたいです。
そもそも、条件付き書式とVBAの優先順位さんが設定している条件付き書式とは、どのようなものなのでしょうか?
一つのセルに対して、複数の条件付き書式を同時に与えることができますので、「条件付き書式の登録順がどうなっているか」「条件を満たす場合は停止を使っているか」といった複合条件に応じて考える必要があります。
現時点で設定されている条件付き書式の一覧は、[ホーム]リボンの[スタイル]-[条件付き書式]-[ルールの管理]で確認できますね。
条件付き書式より優先できる方法があるかと質問させていただきました。
ありません。
同じスタイル（たとえば背景色）が、セル自体の書式と条件付き書式とで同時に適用された場合、条件付き書式が優先されますし、条件付き書式が複数あれば、より優先度の高いものが優先利用されます。
より優先度の強い条件付き書式を与えるようにするなり、条件の組み合わせを見直して不要な条件付き書式を除外できるようにするなり、あるいはそもそも条件付き書式を消してセル書式のみで表現するような VBA を組むなどして対処してみてください。
2021年2月20日土曜日 4:42
「条件付き書式とVBAの優先順位」ではなくて、「条件付き書式と書式設定の優先順位」というべきでしょう。VBAからでなくても、条件付き書式が設定されているセルについて手動で書式設定で塗りつぶしを変更しようとしても、できないはずです。
根拠記事を見つけられないのですが、経験的な知識として言うと、どうも条件付き書式が設定されているセルは、セルの色を変更できないようです。条件付き書式の方が、通常の書式設定より優先順位が高いということなのだろう、と理解しています。だいたいまっさらのワークシートのセルも無色という書式設定がされているのであって、もしも通常の書式設定の方が優先されるとすると、条件付き書式設定が機能しようがなくなるわけですから、条件付き書式設定の方が優先順位が高いという理解は合理的だろうと思います。間違っているかもしれませんが。
この様な理解からすると、次の例の様に、右クリックされたセルの条件付き書式を削除して着色又は無色化することとなろうかと思います。もちろん、無色化で条件付き書式を設定し直す必要があるならば、条件付き書式を設定するコードを追加する必要があります。
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .FormatConditions.Count = 0 Then
MsgBox "条件付き書式は設定されていないので、そのまま着色/無色化します。"
Else
MsgBox "設定されている条件付き書式を削除して、着色/無色化します。"
.FormatConditions.Delete
End If
With .Interior
If .ColorIndex = xlNone Then
.ColorIndex = 7
Else
.ColorIndex = xlNone
End If
End With
End With
Cancel = True
End Sub
なお、条件付き書式設定を削除するのではなくて、条件付き書式設定を変更して目的の着色/無色化をするという方策も考えられますね。
ところで、冒頭の「カレンダー上での仕様です」という意味が良く分からないのですが…^^;;;、何を伝えられたいのでしょうか?。
2021年2月20日土曜日 5:59
cancel時には元の書式に戻したいのですが
条件付き書式を設定するコードはどう調べたらよいでしょうか？
またどこに挿入するのでしょうか？
確認画面を消す方法はありますか？
色々申し訳ありません。よろしくご教授ください
2021年2月20日土曜日 7:35
「Cancel時には元の書式に戻したいのですが」とのことですが、どうやってCancelを受け取るおつもりなのかが分かりませんし、元の書式が何の事か(いわゆる一般的な書式か条件付き書式か)もわかりません。セルを右クリックしただけでは、何も表示されないからです。小生が提示したコードであれば、メッセージが表示されますが、このMsgBox"ほげほげ"を「myRetVal=MsgBox(”ほげほげ”,vbYesNoCancel)」とすれば、変数ｍｙRetValにvbYes、vbNo、vbCancelを受け取れますので、それに応じた処理を記入して実行させることができます。
条件付き書式を設定するコードですが、「Excel vba 条件付き書式を設定する」あたりでググると色々出てくると思います。
どこに挿入するのでしょうか、とのことですが、何をどうしたいという処理の手順を日本語で1処理1行づつ示していただくとかしないと、ちょっとこちらではどこに何を挿入するのかを尋ねているのか良く分からないので、判断のしようがないというか…。条件付き書式を設定するコードをどこに挿入するか、という事であれば、条件付き書式を設定したいところ(行間)に挿入する、とまでしか、今のところは申し上げようがないです。
「確認画面を消す方法」の確認画面も意味が分からないのですが、何のことでしょうか？メッセージのことであれば、MsgBox"ほげほげ"の文章の冒頭に「’」を付けてコメント行にしてしまえばいですが…。
こちらこそ、何をどうしたいのかがよく分からないので、的確なご返事ができなくて申し訳ありません。
2021年2月20日土曜日 11:47
詳しく丁寧にありがとうございます。詳しい言葉がわからずうまく伝わりませんでした。申し訳ありません。
まず行った内容をお伝えしますのでもう一度教えていただきたいです。
まずカレンダーを作成しました。そのカレンダーの日にちをダブルクリックして日にちを指定します。この指定日は条件付き書式設定で塗りつぶしにしてあります。この指定日は変更しても塗りつぶしができています。
その指定日の35日前と42日前（別のセルで関数で算出）を条件付き書式設定でそれぞれ塗りつぶすようにしました
本日（条件付き書式設定で塗りつぶし）から指定日までの間の何日かを選択するために下記のセルに塗りつぶしたかったのです
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target.Interior
Cancel = True
If .ColorIndex = xlNone Then
.ColorIndex = 7
Else
.ColorIndex = xlNone
End If
End With
Cancel = True
End Sub
ここで抜けていたのですがこの右クリックでセルの選択は複数選択であり、何日間か選択するうえで変更する場合もあるのでもう一度右クリックすると元に戻る（塗りつぶしが消える）設定にした予定でした。
しかし条件付き書式設定で塗りつぶしたセルは変更できませんでしたので条件付き書式より優先できる方法があるかと質問させていただきました。
ご回答の方法を試し、セルの変更はできたのですが変更のためもう一度右クリックするとに塗りつぶしが消えてしまい元のセルにならなくなってしまうので、できれば再度右クリックすると元に戻ることはできないでしょうか？
にわか知識で作っているため説明が下手で申し訳ありません。どうぞよろしくお願いします
2021年2月20日土曜日 16:51
魔界の仮面弁士さま、教えていただきたいのですが、VBAで条件付き書式を設定する場合、range.FormatConditions.Addで設定する方法しかないのでしょうか? 既に目的のセルに設定されている条件付き書式を Set myFC=range.FormatConditions で変数に取得しておいて、後で必要に応じてその変数を使って再設定するような(例えば Set range.FormatConditions = myFC のような)方法がないものかと記事を探したり試行錯誤もしてみましたが、記事は見つからず試行錯誤もうまくいきませんでした。
2021年2月20日土曜日 21:51
他の人も指摘している通り、条件付き書式が優先です。
条件付き書式とVBAによる動的な塗りつぶしを併用するのは、ロジック的に無理があるような気がします。
この際、条件付き書式で統一されては如何ですか？
例えば、隠しシート（Visibleプロパティが xlSheetHidden 又は xlSheetVeryHidden のシート）を１つ用意しておいて、そちらに条件管理用の値を;納するとか。
仮に、隠しシートの名前を「hidden」とするなら、現行のイベントプロシージャを以下のようにすることで、対応する同セル番地の値を 空白 ⇔ 1 に切り替えることができます。
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Worksheets("hidden").Range(Target.Address)
If Val(.Item(1, 1).Value) = 1 Then
.ClearContents
Else
.Value = 1
End If
End With
Cancel = True
End Sub
そして、カレンダーの条件付き書式設定において、隠しシートのセルを参照する条件式を追加すれば良いと思います。
2021年2月20日土曜日 23:19
range.FormatConditions.Addで設定する方法しかないのでしょうか?
FormatConditions の既定のプロパティから、割り当て済みの条件付き書式をオブジェクトとして取得できますね。
If Sheet1.Cells.FormatConditions.Count > 0 Then
'最初の条件付き書式を「斜めグラデーション背景」に変更
Dim fc As FormatCondition
Set fc = Sheet1.Cells.FormatConditions(1)
fc.Interior.Pattern = xlPatternLinearGradient
Dim lg As LinearGradient
Set lg = fc.Interior.Gradient
lg.Degree = 30
lg.ColorStops.Clear
lg.ColorStops.Add(0).Color = vbCyan
lg.ColorStops.Add(0.9).Color = vbBlue
lg.ColorStops.Add(1).Color = vbBlack
End If
既に目的のセルに設定されている条件付き書式を Set myFC=range.FormatConditions で変数に取得しておいて
Dim A As FormatConditions '条件付き書式の一覧を示すコレクション
Dim B As FormatCondition '条件付き書式のルールを表すオブジェクト
を混同していませんか?
2021年2月21日日曜日 6:41
魔界の仮面弁士さま、横からの質問にお答えいただき、恐縮です。ありがとうございます。
Add以外に、既存の条件付き書式を変更することはできる、という例を示していただけたと理解します。FormatConditionsは、取得はできるけれども、条件付き書式をdeleteしたセルに丸ごとコピペするような設定方法はない、Addを使って個別のスペックを指定しなければならない、と理解しました。
自分としては、FormatConditions とFormatCondition とを混同はしていないと思うのですが、魔界の仮面弁士さまが記している「条件付き書式のルールを表すオブジェクト」という説明内容を間違って理解している可能性はあるかもしれません。条件付き書式は、必要に応じて使って思った様に動いているからよしとしているものの、Excelのバージョン毎に少しずつ変わっている様にも思いますし、もう少し手動ででもVBAででも、条件付き書式の設定の勉強をし直して理解を深めないと、自在に使いこなすにはなかなか分かりにくいツールであるように思います。ありがとうございました。
2021年2月21日日曜日 8:22
ほかの方からもご返事が出ていますが、小生からは簡単なサンプルをご提示してご返事とさせていただきます。
条件付き書式が設定されているセルの場合は、条件付き書式を削除して通常の書式設定で塗りつぶしをして、2度目の右クリックでは、通常の書式設定の塗りつぶしをなしにして、元の条件付き書式を設定する、という処理を当初考えたのですが、元の条件付き書式を設定しようにもこちらではどのような条件付き書式なのかわからない(幾つ設定されているのかとかどんなタイプかとか細かく取得していけば可能かもしれないけれどとっても面倒そう…)ので、できませんでした。
そこで、1回目の右クリックでは、選択セル範囲に上位の条件付き書式を設定して紫色に塗りつぶし、2回目の右クリックでは、選択セル範囲の上位の条件付き書式だけを1つ削除する、という処理を考えました。
ただ、問題は、1回目の右クリックの選択セル範囲と2回目の右クリックの選択セル範囲が異なる場合があり、1回目の右クリックの選択セル範囲に含まれているけれども2回目の右クリックの選択セル範囲には含まれていないセルは、塗りつぶされたままとなってしまいます。また、小生のサンプルでは、ブックを閉じると1回目に右クリックしたセル範囲のデータが消えてしまいますので、再び開いた時に2回目の右クリックのつもりで右クリックしても、それは1回目の右クリックとなるだけなので、塗りつぶしをなしにすることができません。それも困ったことなので、意図せず塗りつぶしセルが残った場合に1セルづつ右クリックして塗りつぶし無しにする処理も織り込んでみました。
Option Explicit
Private lastTgtRngData() As Long 'x=1～2、y=1～選択セル数、x;1=セルの行番号、2=セルの列番号
Private myRightClickFlag As Integer '1又は0；奇数回目の右クリックした状態=1、右クリック前又は偶数回目の右クリックした状態=0
Private tgtRngsCount As Long '選択セル数
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim myRetVal As Variant
Dim R As Range
Dim i As Long
Dim j As Long
Cancel = True
If myRightClickFlag = 0 Then
'1回目又は奇数回目の右クリックの場合
If Target.Cells.Count = 1 And Target.FormatConditions.Count > 0 Then
myRetVal = MsgBox("設定されている条件付き書式の数は" & Target.FormatConditions.Count & "です。" _
& vbCrLf & "選択セルの最上位の条件付き書式を削除しますか?", vbYesNo ; vbQuestion)
If myRetVal = vbYes Then
Target.FormatConditions(1).Delete
Exit Sub
Else
End If
Else
End If
MsgBox "選択セル範囲のセルを紫色に塗りつぶします。"
tgtRngsCount = Target.Cells.Count
ReDim lastTgtRngData(2, tgtRngsCount)
i = 1
For Each R In Target
With R
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(LEN(" & R.Address(0, 0) & _
")>0, LEN(" & R.Address(0, 0) & "=0))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 16711935
.Interior.TintAndShade = 0
.StopIfTrue = False
End With
lastTgtRngData(1, i) = .Row
lastTgtRngData(2, i) = .Column
i = i ; 1
End With
Next R
myRightClickFlag = 1
Else
'2回目又は偶数回目の右クリックの場合
MsgBox "選択セル範囲のセルの紫色の塗りつぶしをなしにします。"
For Each R In Target
With R
For i = 1 To tgtRngsCount
If .Row = lastTgtRngData(1, i) And .Column = lastTgtRngData(2, i) Then
'1回目に右クリックしたセル範囲のセルの場合
If .FormatConditions.Count > 0 Then
.FormatConditions(1).Delete
End If
Else
'1回目の右クリックセル範囲ではないセルでない場合；何もしない
End If
Next i
End With
Next R
Erase lastTgtRngData
myRightClickFlag = 0
tgtRngsCount = 0
End If
End Sub
なお、自分でも動作確認で右クリックしているうちに、奇数回目か偶数回目か分からなくなって来たりしましたので、メッセージが表示されるようにしてみました。邪魔くさかったら削除してください。
また、当初、カレンダーのセル範囲を選択して右クリックするという想定で、セルの値が正数の場合着色するコードにしていましたが、そうすると空欄を選択して右クリックした場合にも条件付き書式が設定されるが着色されないので、下手に空欄を右クリックして行くとワークシートに未着色なので簡単には把握できないけれど条件付き書式がいくつも設定されているという状態になりかねないため、右クリックした選択セルの値が空でも空でなくても着色するように条件付き書式を手直ししました。
以上、ご参考まで。
2021年2月22日月曜日 5:33
難しくて私のレベルでは理解できないところがあります
教えていただいた処理ですと2回目のクリック時に「条件付き書式は設定されていないのでそのまま着色・無色化します」と出るのですがこの時に元の条件付き書式に戻すことはできますか？
何度も申し訳ありません。
またもし条件付き書式でなくダブルクリック時に同時にセルを変更ができますか？
カレンダーでダブルクリックで取得日を選択し取得日をL1のセルに返してあります。L1のセルからN1に42日前N2に34日前この間を（黄色の文字に）、P1が35日前からP2が取得日（L１）前日の間を（緑の文字に）することは可能でしょうか（塗りつぶしは無し）
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("カレンダー").Range("l1").Value = Target.Value
Range("l1").Select
End Sub
そのあとで紫に塗りつぶす
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target.Interior
Cancel = True
If .ColorIndex = xlNone Then
.ColorIndex = 7
Else
.ColorIndex = xlNone
End If
End With
Cancel = True
End Sub
で文字色は変更せず塗りつぶしだけができるということはできないでしょうか？
説明がしにくく申し訳ありませんがどうぞよろしくお願いします。
2021年2月22日月曜日 9:50
>教えていただいた処理ですと2回目のクリック時に「条件付き書式は設定されていないのでそのまま着色・無色化します」と出るのですがこの時に元の条件付き書式に戻すことはできますか？
出来ません。というのは、元の条件付き書式は削除されており、元の条件付き書式がどんなものなのか分からないので、できません。もしも分かっていれば、再度、設定すればよろしいので、できます。
小生には、元の条件付き書式がどんなものなのか分からない(分かろうとして分析してデータを取るVBAコードを書けば可能ではないかとは思いますが、申し訳ないですが、面倒で今それをやるモチベーションがわきません。)ので、できないわけです。
それで、2月21日のコード(最優先の条件付き書式を設定して、ColorIndex = 7に相当する色で着色するようにしたもの。2回目の右クリックで、設定した最優先の条件付き書式を削除するので、もしも条件付き書式が設定されていたセルであれば、その元の条件付き書式が最優先になって生きる。)をお示ししました。
>またもし条件付き書式でなくダブルクリック時に同時にセルを変更ができますか？
右クリックでもダブルクリックでも同じです。条件付き書式が設定されていてその条件がTrueで書式が適用されていると、通常の書式設定でその書式を変更することはできません。条件付き書式が設定されていてもその条件がFalseで書式が適用されていないと、通常の書式設定でその書式を変更することができます。
通常の書式設定でも条件付き書式設定でも、文字色と塗りつぶしは別々に設定できるので、条件付き書式設定で塗りつぶし色が指定されているとき、通常の書式設定で塗りつぶし色を変えることはできませんが、文字色を変えることはできます。もしも条件付き書式が設定されていて文字色が指定されて適用されていると、通常の書式設定で文字色の変更はできないけれど、塗りつぶし色は変更できます。
しかし、条件付き書式設定との関係で処理を場合分けして通常の書式設定で書式を設定したり最優先の条件付き書式で書式を設定したりするコードを記載するのは、とても煩雑になると思いますので、2月21日にお示ししたコードの様に、条件付き書式だけを設定又は削除することとして、最初の右クリックで最優先の条件付き書式を設定して、塗りつぶし色を変更したり、最初のダブルクリックで文字の色を変更する、次の右クリックやダブルクリックでは設定したその条件付き書式を削除して元の条件付き書式があればそれが生きるようにする、というのがよろしいのではないかと思います。ただ、右クリックとダブルクリックとで塗りつぶしと文字色と2つの条件付き書式を設定すると、右クリックとダブルクリックとの順番が変われば最優先の条件付き書式の内容も変わりますから、解除する条件付き書式をどうやって区別して指定するか、ちょっと知恵を絞らなければならなくなりそうですね。こんな風に考えてくると、塗りつぶしと文字色と指定する UserFormを使ってセットで条件付き書式がセット出来ればよいのかと思いますが、ちょっと今はそれができるかどうか、未確認です。