Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Excel 2021)
FormatConditions.Addで2つの条件
投稿日時: 24/02/11 14:03:31
投稿者: Nubo

以下の関連質問です。
https://www.moug.net/faq/viewtopic.php?t=82647
 
 
セルの値が「=OR(RC[3]>1」または先頭がスペースで始まる場合に、黄色の塗りつぶしを目的ですが
上手く処理できません。
 
With .Range("B2").Resize(dic.Count).FormatConditions.Add(xlExpression, Formula1:="=OR(RC[3]>1, LEFT(R[0]C[0], 1)="" "")")
    .Interior.Color = vbYellow
End With
 
エラーは出ませんが、空白スペースがヒットしません。
現在は、半角スペースを想定していますが希望としては全角のスペースにも対応したい
 
どこが間違っていますか ?

回答
投稿日時: 24/02/11 15:22:52
投稿者: WinArrow

全角スペース、半角スペース込み

    With .Range("B2").Resize(dic.Count)
        .FormatConditions.Delete
        With .FormatConditions
            .Add xlExpression, _
                Formula1:="=OR(D2>1,ASC(LEFT(B2,1))="" "")"
        End With
        .FormatConditions(1).Interior.Color = vbYellow
    End With

投稿日時: 24/02/11 16:06:36
投稿者: Nubo

回答ありがとうございます。
 
With .Range("B2").Resize(dic.Count)
        .FormatConditions.Delete
’---------
        With .FormatConditions
            .Add xlExpression, _
                Formula1:="=OR(D2>1,ASC(LEFT(B2,1))="" "")"
        End With
’------------
        .FormatConditions(1).Interior.Color = vbYellow
    End With
 
コードが読み解けないので間違っているとは思いますが、
 
1)一度、条件付き書式を解除して改めて条件付き書式を付けていますが
解除コードは必要なのでしょうか?
そのまま、条件付き書式を記載するのはまちがいですか ?
 
2)’------------と’---------------に囲まれたのが(1)相当なので
以下のように書き換える事はできますか ?
 
 .Range("B2").Resize(dic.Count).FormatConditions.Delete
 .Range("B2").Resize(dic.Count).FormatConditions.Add xlExpression, _
       Formula1:="=OR(D2>1,ASC(LEFT(B2,1))="" "")".Interior.Color = vbYellow
 
(with が多くなるとどこで繋がるかが良くわからなくなりました。)
 
3)RC形式でなくA1形式で処理されるコードに変更されていますが
私としては日ごろ使わないRC形式でなく絶A1形式の方がなじみが良いのですが
処理範囲が変わっていく今回のようなケースでもA1形式が利用できるのが不思議に思えます。
 
 
条件は、以下との回答ですが
Formula1:="=OR(D2>1,ASC(LEFT(B2,1))="" "")"
 
C[3]>1 なので E2>1 に変更してこの条件はOKなのですが
ASC(LEFT(B2,1))="" "" は、ヒットしないのか? 何も書き出されませんでした。
(間違いなく、該当する半角スペースで始まる対象が存在します。)
 
後出しで条件を替えるのは申し訳ないのですが
 先頭がスペースで始まる場合は、黄色でなくVBGreenに変更したいです。
 (=OR(RC[3]>1の条件は、VbYellowで変更なしです。)
 

回答
投稿日時: 24/02/11 18:15:21
投稿者: WinArrow

条件付き書式の削除(Delete)は、こちらでテスト時に記述したもので、不要です。
でも、そのままでも、害にはなりません。問題はありません。
  
  
条件付き書式は、設定する書式ごとに1つ設定します。
2つ以上の条件付きを設定する場合は、優先順位を指定することができます。
  
今回の場合、vbYellow とVBGreen のどちらを優先するか決める必要があります。
下記コードは、2つの条件が成立する場合、vbYelowの方を優先するようになっていますう。
  
先頭1文字が「半角」Or「全角」スペースを判断するセルは、B2でよいのですか?
下記コードは、B2にしてあるので、違っていたら、修正してください。
  
  
先ほどのコード、一部間違いがありますが、下記コードで修正してあります。
  

    With .Range("B2").Resize(dic.Count)
        .FormatConditions.Delete
        CNT = 0
        With .FormatConditions
            .Add xlExpression, _
                Formula1:="=E2>1"
            CNT = CNT + 1
        End With
        With .FormatConditions(CNT)
            .Interior.Color = vbYellow
            .StopIfTrue = True
        End With
        
        With .FormatConditions
            .Add xlExpression, _
                Formula1:="=LEFT(ASC(B2),1)="" """
            CNT = CNT + 1
        End With
        With .FormatConditions(CNT)
            .Interior.Color = vbGreen
            .StopIfTrue = True
        End With
    End With

 

回答
投稿日時: 24/02/11 21:32:21
投稿者: WinArrow

引用:

(with が多くなるとどこで繋がるかが良くわからなくなりました。)

 
インデントをキチンと付ければ、可読性が向上します。
メンテナンス性も向上します。
 
>With .Range("B2").Resize(dic.Count)
この部分が適用範囲となります。
適用範囲の左上セル(B2)と同じ行を、条件式の中の参照セルの行とします。
これにより、先頭セル以外の他のセルは、相対的に条件式が適用されます。
また、条件式を変更/修正する場合は、先頭行の1つだけ変更すればよいです。
逆にいうと、先頭行以外は変更できません。
 
 
 
 
 
 
 

投稿日時: 24/02/12 08:41:02
投稿者: Nubo

 何度もありがとうございます。
 
便利な StopIfTrue = True を今回覚えました。
(以下のような解釈で問題ないと思いますが?)
 
やはり、withが多くなると読めなくなるので以下に落ち着きました。
 
   With .Range("B2").Resize(dic.count)
                  .FormatConditions.Add xlExpression, Formula1:="=LEFT(ASC(B2),1)="" """
                  cnt = cnt + 1
                         
                  .FormatConditions(cnt).Interior.Color = vbGreen
                  .FormatConditions(cnt).StopIfTrue = True
                   
                  'StopIfTrue = True --------------
                  '複数の条件を設定してある場合に、上に書いた条件から順番に評価し
                  '最初の条件を満たして書式をセットしたら、
                  'そこから下の条件は優先度が低いと解釈されて
                  '優先度が低い方の条件にマッチしても書式が適用される事はない
                         
                  .FormatConditions.Add xlExpression, Formula1:="=E2>1"
                  cnt = cnt + 1
                         
                  .FormatConditions(cnt).Interior.Color = vbYellow
                  .FormatConditions(cnt).StopIfTrue = True
         
      End With

回答
投稿日時: 24/02/12 10:10:09
投稿者: WinArrow

引用:

                  '最初の条件を満たして書式をセットしたら、
                  'そこから下の条件は優先度が低いと解釈されて
                  '優先度が低い方の条件にマッチしても書式が適用される事はない

結果は、同じになるが、少し解釈が違うと思います。
  
                  '最初の条件を満たして書式をセットしたら、
                  'そこから下の条件は照合せずに処理を抜ける。
  
照合するだけで処理時間が掛かるので、無駄な処理を避けることができる、
  
蛇足ですが、
Excel2003バージョン以前にも、条件付き書式機能は存在したが、
設定数が3つまで、適用範囲という機能がありませんでした。
適用範囲が使えないと、条件変更の場合、適用した全てのセルの条件式を個々に変更しなければならない。
Excel2007を使い始めたころ、先頭セル以降の条件はどうすんだ!と悩んだことがありました。
 

回答
投稿日時: 24/02/12 10:24:40
投稿者: WinArrow

>優先度
について、誤解がないように
 
条件評価の優先度は、設定順番です。(上から)
手操作で設定した場合は、後から入力した条件が上に並びます。
 
書式適用の優先度は、
.StopIfTrue = False (default)
の場合、下が優先することがある。
 
今回の場合、
条件式が別セルを参照しているので、
E2>1 と LEFT(ASC(B2,1))=" "が両立すると後の書式が適用されます。

投稿日時: 24/02/12 11:48:51
投稿者: Nubo

解説をありがとうございます。
 
 
>'最初の条件を満たして書式をセットしたら、
>'そこから下の条件は照合せずに処理を抜ける。
 
なるほど、最初の条件に適合すればそれ以下の条件参照はしないで無視する
と思って解決と思いましたが 
 
>今回の場合、
>条件式が別セルを参照しているので、
>E2>1 と LEFT(ASC(B2,1))=" "が両立すると後の書式が適用されます。
 
条件を判断するセルが違うと最初の条件に適合しても
それ以下は参照しないで無視する事は無く
後ろの条件に適合していれば後の書式が適用されるとは
もう何とも判りにくい判断基準です。
 
では、条件を判断するセルが同じと言う条件で
最初の条件に適合すればそれ以下の条件参照はしないで無視する
と言う事でしょうか ?

回答
投稿日時: 24/02/12 12:59:18
投稿者: WinArrow

引用:

るほど、最初の条件に適合すればそれ以下の条件参照はしないで無視する

>無視
.StopIfTrue = True
は、以降の処理をスキップするように、あなたが命令しているんですよ。
VBAが勝手に無視しているわけではありません。
 
2番目の質問
>.StopIfTrue = False (default)
>の場合、
という前提で話をしています。

回答
投稿日時: 24/02/12 14:00:00
投稿者: 半平太

>>条件式が別セルを参照しているので、
>>E2>1 と LEFT(ASC(B2,1))=" "が両立すると後の書式が適用されます。
 
そんなこと起こる訳ないでしょ。
一種類の書式(塗りつぶしの色とか)は、優先順位どおり適用される。

投稿日時: 24/02/12 14:46:21
投稿者: Nubo

 
説明を受けて、合点が行きました。
 
内容了解しました。