Excel (VBA)

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

 
(Windows 7 Home Premium : Excel 2010)
条件付き書式で設定した塗りつぶしの色の取得
投稿日時: 20/05/17 12:06:17
投稿者: shimoichimabu

いつもお世話になっています。
 
条件付き書式で100以上の値の場合は黄色の塗りつぶし(背景)になるよう、設定しています。
今回、塗りつぶしなしのセルがあれば、Aという文字を書き込むコードを検討しています。
なお、白色の塗りつぶしは設定していません。
 
・・・・・・・・・・・・・・・・
If Cells(7, N).DisplayFormat.Interior.Color = 16777215 Then
     
   Cells(7, N).Value = "A"
         
End If
     
・・・・・・・・・・・・・・・・・
 
一応、今回のケースで、このコードで問題なかったです。
さて、今回のケースとは別に、「塗りつぶしなし」と「白色」を判定する場合、
見た目は同じですが、厳密には違うと思うのですが・・・。
試しに、条件付き書式で塗りつぶしの白色と塗りつぶしなしのセルを
 MsgBox Rnge("A1").DisplayFormat.Interior.Color
で確認すると、16777215 という数値が返って来ました。
DisplayFormat.Interior.Colorでは両者の区別がつかない?ということがわかりました。
現実には、両者を使い分けて使うケースは殆どないと思いますが、向学のために投稿致しました。
両者を区別するコマンドはあるのでしようか?

回答
投稿日時: 20/05/17 13:25:56
投稿者: WinArrow
投稿者のウェブサイトに移動

色を取得するよりも
その色をセットする条件以外という条件を浮けばよいでしょう。
 
100以上という条件で黄色をセットしているのだかr
100未満という条件で抽出
ということです。

回答
投稿日時: 20/05/17 17:39:19
投稿者: simple

Sub test()
    Dim p1 As Interior 
    Dim p2 As Interior 
    Set p1 = Range("A1").DisplayFormat.Interior
    Set p2 = Range("A2").DisplayFormat.Interior
    Stop
End Sub
といったテストコードで、ローカルウインドウで両者のプロパティを比較すると
違いが色々とわかりますよ。
後学のためになりますでしょうか。

投稿日時: 20/05/17 19:34:26
投稿者: shimoichimabu

 WinArrowさん、simpleさん回答ありがとうございます。
 
以下のように、TESTで、条件付き書式を設定しました。
 
A1  条件付き書式⁼数式:=A1=""  書式:黄色塗りつぶし 結果⇒ color:255   colorIndex:3
A2  条件付き書式⁼数式:=A2=""  書式:白色塗りつぶし 結果⇒ color:10092543 colorIndex:36
A3  条件付き書式⁼数式:=A3=""  書式:塗りつぶしなし 結果⇒ color:10092543 colorIndex:36
 
結果、白色塗りつぶしと塗りつぶしなしでは、
colorとcolorIndexの数値が同じで、やはり区別がつきませんでした。

回答
投稿日時: 20/05/17 20:10:22
投稿者: simple

>A1  条件付き書式⁼数式:=A1=""  書式:黄色塗りつぶし 結果⇒ color:255   colorIndex:3
すいません、それはコードで出力した結果なんでしょうか?
そうであればそのコードを示してもらえますか?
 
黄色なのに、Color=255(赤)というのも理解できませんし、
すべてのcolorIndexが3というのも私の環境では再現しません。(OS、Excelとの同環境です)

投稿日時: 20/05/17 22:31:04
投稿者: shimoichimabu

simpleさん回答ありがとうございます。
 
>ローカルウインドウで両者のプロパティを比較すると
↑この意味を私が誤解していたかもしれません。
初歩的なことを聞いてすみませんが、Debug.Printのことでしようか?
 
もう一度、各セルの条件付き書式を設定し直しました。
コードは下記の通りです。
 
Sub Macro1()
Dim p1 As Interior
Dim p2 As Interior
Dim p3 As Interior
 
    Set p1 = Range("A1").DisplayFormat.Interior
    Set p2 = Range("A2").DisplayFormat.Interior
    Set p3 = Range("A3").DisplayFormat.Interior
    Stop
End Sub
 
検証のやり方は、
Range("A1").DisplayFormat.Interior
Range("A2").DisplayFormat.Interior
Range("A3").DisplayFormat.Interior
をクイックウオッチに設定し、プログラムを実行しました。
 
その結果、
A1 黄色塗りつぶし color:65535   colorIndex:6
A2 白色塗りつぶし color:15921906 colorIndex:2
A3 塗りつぶしなし color:16777215 colorIndex:-4142
 
条件付き書式を再設定した結果がこうなので、最初の条件付き書式の設定は間違っていました。
とりあえずは、区別できることがわかりました。
塗りつぶしなしの colorIndex:-4142 がマイナスになっていますが、気になります。

回答
投稿日時: 20/05/18 09:01:21
投稿者: simple

ローカルウインドウは、
・オブジェクト変数の持っているすべてのプロパティとその値
を一覧で見られるものなので、デバッグには必須のものです。
https://sites.google.com/site/compositiosystemae/home/vbaworld/gatetovba/vbe/localwin
を参照してください。
 

Sub test()
    Dim p1 As Interior 
    Dim p2 As Interior 
    Set p1 = Range("A1").DisplayFormat.Interior
    Set p2 = Range("A2").DisplayFormat.Interior
    Stop
End Sub

を実行すると、Stopの所で必ず止まるはずなので、その状態で、ローカルウインドウを見て下さい。
p1とp2オブジェクトにそれぞれついている十字の印をクリックすると
それらのオブジェクトの持つプロパティとその値が一覧できます、と申し上げています。

回答
投稿日時: 20/05/18 09:58:15
投稿者: simple

上の書き方だと、ローカルウインドウはオブジェクト変数に限定したものと誤解されそうですが、
オブジェクト変数の場合は特に有用だということです。
 
・そもそもどんなプロパティを持ったオブジェクトなのか
・現時点でそのプロパティの値はどうなっているか
が一覧的に見られるということです。
 
さらに子のオブジェクトをプロパティで持っている場合は、階層構造で表示されますので、
そのオブジェクトの構造を見たい場合には必須のツールになります。
(もっとも、オブジェクトを返すメソッドなどというものもあり、
メソッドはローカルウインドウで表示される対象外なので、すべてを網羅できるわけではありませんが、
たいていの場合はそれで用が足ります。)
 
あとは、配列変数の場合ですね。
Debug.Printでは配列を一括出力できず、要素指定しないといけないので面倒ですが、
ローカルウインドウであれば、各要素が表示されていますから、内容を確認するのに便利です。
 
文字変数や数値変数であれば、変数名にカーソルを置いて簡単に内容がわかりますが、
オブジェクトを使ったコードでは、これなしにデバッグはできないといったレベルのものです。

回答
投稿日時: 20/05/18 10:31:06
投稿者: WinArrow
投稿者のウェブサイトに移動

「Color」と「ColorIndex」の違い
 
Excel2007以降で、使用できる色が大幅に変更になりました。
 
「ColorIndex」は「カラーパレット」の番号です。
カラーパレットは、1〜56の番号で識別しています。
カラーパレットには、標準設定の色がありますが、ユーザーで標準とは違う色を設定することができます。
あくまでも色そのものではなく、パレットの番号であるため、
パレットの番号は同じでもPCによっていろが異なる可能性がある。…おいうもん愛展があります。
 
 
一方「Color」プロパティは、約1677万色が使えます。
 
運用としては、「Color」と「ColorIndex」を混同して使わない方が安全です。
但し、セルの背景を「塗りつぶしなし」ンするなどの場合は
「ColorIndex」の方がわかりやすいです。
 
 
 
 

投稿日時: 20/05/18 14:37:04
投稿者: shimoichimabu

simpleさん、WinArrowさん回答ありがとうございます。
 
>Stopの所で必ず止まるはずなので、その状態で、ローカルウインドウを見て下さい。
実際のやり方がわかりました。
 
>パレットの番号は同じでもPCによっていろが異なる可能性がある。
そういうことがあるのですね。
 
>セルの背景を「塗りつぶしなし」ンするなどの場合は「ColorIndex」の方がわかりやすいです。
これに関して、調べましたら、
Range("A1").DisplayFormat.Interior.ColorIndex = xlNone というコードがあり、
 
If Range("A1").DisplayFormat.Interior.ColorIndex = xlNone Then
    MsgBox "条件付き書式の塗りつぶしなしの設定です。"
End If
 
条件付き書式の塗りつぶしなしの設定でTESTすると、黄色・白色の塗りつぶしには
反応せず、塗りつぶしなしにはメッセージが出て、正しく動作しました。
DisplayFormat.Interior.ColorIndex = xlNone が使えるなら、
ColorIndexで判定しなくても、xlNoneでOKということになりますが、
どうでしようか?

回答
投稿日時: 20/05/18 16:20:18
投稿者: 半平太

MsgBox "条件付き書式の塗りつぶしなしの設定です。
        ~~~~~↑~~~~~
    何故そこまで言い切れるんですか?

回答
投稿日時: 20/05/18 20:56:12
投稿者: simple

質問の詳細は見ていませんでしたが、具体的なプロパティの意味もさることながら、
ローカルウインドウの存在を知ったことは、
その何十倍、何百倍もの価値があるような話かと思いました。(余りに冷静で拍子抜けですね)
今後、より複雑なことをしていこうとしたら、きっと役立つはずです。
 
ちなみに、DisplayFormatプロパティはExcel2010で導入されたものですね。
注意点は、これは条件付書式でなく手でつけたものも判別の対象になるので、
条件付き書式であろうがなかろうが、実際に見えている色などを調べるのに使えます。
半平太さんのご指摘は、そういう意味だろうと思います。
 
メッセージの"条件付き書式の塗りつぶしなしの設定です。"について言及すれば、
設定じゃなく、結果ですよ。
条件付き書式は、まさに条件によって結果が違うわけで、
DisplayFormatは条件設定を云々しているのではなく、
結果がどうなっているかということですね。
承知はされているんでしょうけども。

投稿日時: 20/05/18 21:25:44
投稿者: shimoichimabu

半平太さんご助言ありがとうございます。
 
まず、条件付き書式のローカルウインドウの数値を修正します。
A2 白色塗りつぶし color:15921906 colorIndex:2
       ↓
A2 白色塗りつぶし color:16777215 colorIndex:2
 
>何故そこまで言い切れるんですか?
ご指摘の通りです。
 
条件付き書式ではなく、通常の
B1 黄色:塗りつぶし color:65535   colorIndex:6
B2 白色:塗りつぶし color:16777215 colorIndex:2
B3 何も設定なし   color:16777215 colorIndex:-4142
 
で、設定しましたが、ローカルウインドウの数値は条件付き書式の場合と同じでした。
 
If Range("B1").DisplayFormat.Interior.ColorIndex = xlNone Then
     MsgBox "塗りつぶしなしの設定です。"
EndIf
 
If Range("B2").DisplayFormat.Interior.ColorIndex = xlNone Then
     MsgBox "塗りつぶしなしの設定です。"
EndIf
 
If Range("B3").DisplayFormat.Interior.ColorIndex = xlNone Then
     MsgBox "塗りつぶしなしの設定です。"
End If
 
を実行すると、黄色:塗りつぶし、白色:塗りつぶしの場合はMsgBoxのメッセージが出ない。
何も設定なし の場合はMsgBoxのメッセージあり。
ということは、条件付き書式および通常の塗りつぶしなしの判定に
DisplayFormat.Interior.ColorIndex = xlNone
が使用できるということになるのでしようか?
ちょっと、ややこしくなって混乱していますが・・・・。

回答
投稿日時: 20/05/19 08:25:34
投稿者: MMYS

 
色とは光の三原色。赤、緑、青なのはご存知だと思います。
  
そして、赤、緑、青それぞれに8ビットで、その8ビットまとめて24ビットで構成します。
ですから、Colorプロパティは、16進数で表現するのが分かりやすいです。
  
B1 黄色:塗りつぶし color:00FFFF colorIndex:6
B2 白色:塗りつぶし color:FFFFFF colorIndex:2
B3 何も設定なし   color:FFFFFF colorIndex:-4142
  
  
色とは三原色です。ですから、
・透明な色
・塗りつぶしなし
は存在しません。
  
Colorプロパティは三原色を直接指定します。
原理上、「塗りつぶしなし」の指定や判定は出来ないはず。
  
一方、ColorIndex は色番号で指定します。
三原色で出力ですから、パレット情報から三原色に変換して表示します。
  
ColorIndex で「塗りつぶしなし」と指定した時、
三原色は何色で表現すべきですか。
  
  
ColorプロパティとRGB関数について
http://officetanaka.net/excel/vba/graph/25.htm
 

回答
投稿日時: 20/05/19 13:26:33
投稿者: 半平太

情報を整理してみると、こんなことになるらしい。(かな?)
 
 

操作         ┃Color      ColorIndex           枠線       条件付き書式か     
                ┃                                           素の色かの区別     
━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 白             ┃16777215   場合によるが         消える     普通は区別できない(※) 
                ┃           -4142 にはならない                                 
━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 塗りつぶしなし ┃16777215   -4142                消えない   普通は区別できない(※) 

                 
(※)条件式がEVALUATEで正しく評価できるものなら可能。
   経験則では、ROW関数が使われていると正しく評価できない
   例: =ROW($A$1)=1
     この条件式は、常に成立するので条件付き書式の色が付くが、
         その条件式をEvaluateで評価するとエラーになってしまう。
     なので、色が付いているハズとの判定ができない。

回答
投稿日時: 20/05/19 17:44:05
投稿者: simple

>塗りつぶしなしの colorIndex:-4142 がマイナスになっていますが、気になります。
の箇所について、コメントしておきます。

VBAヘルプ さんの引用:
Interior.ColorIndex プロパティ
備考
色は、現在のカラー パレットのインデックス番号、または次の XlColorIndex クラスの定数を使用します。
xlColorIndexAutomatic
xlColorIndexNone
こうしたところはキチンと目を通された方がよいと思います。
 
また、イミディエイトウインドウで内容を確認すると、こうなります。
?xlColorIndexNone
-4142 
定数をこう決めたと言うことだと思われます。

回答
投稿日時: 20/05/20 21:43:29
投稿者: takesi

こんだけ見れば違いわかる?
 

Sub test()
Dim tgRange As Range
Dim setRange As Range
Dim fc 'As ColorScale
Dim csc As ColorScaleCriterion

Set setRange = Worksheets(1).UsedRange

For Each tgRange In setRange
  Debug.Print tgRange.Address
  Debug.Print Hex(tgRange.DisplayFormat.Interior.Color)
  Debug.Print Hex(tgRange.Interior.Color)
  Debug.Print Hex(tgRange.Interior.ColorIndex)
  If tgRange.FormatConditions.Count > 0 Then
    For Each fc In tgRange.FormatConditions
      Select Case fc.Type
      Case Is = 3
        For Each csc In fc.ColorScaleCriteria
          Debug.Print Hex(csc.FormatColor.Color)
          Debug.Print Hex(csc.FormatColor.ColorIndex)
        Next csc
      Case Is = 1, 2
        Debug.Print Hex(fc.Interior.Color)
        Debug.Print Hex(fc.Interior.ColorIndex)
      Case Else
        Stop
      End Select
    Next fc
  End If
Next

End Sub

投稿日時: 20/05/20 21:57:39
投稿者: shimoichimabu

MMYSさん、半平太さん、simpleさん回答ありがとうございます。
 
>Colorプロパティは三原色を直接指定します。
>原理上、「塗りつぶしなし」の指定や判定は出来ないはず。
確かに、そう云われればそうですね。
 
>ColorIndex で「塗りつぶしなし」と指定した時、三原色は何色で表現すべきですか。
 RGB(255,255,255) です?  でも白色と同じになりますね。
 
半平太さん、助かります。自分でも整理しないと思いつつ、こんがらがっていました。
 
>定数をこう決めたと言うことだと思われます。
そういうことことだったんですね。
 
色々、勉強させてもらいましたが、
 
条件付き書式での「塗りつぶしなし」の判定は
If Range("A3").DisplayFormat.Interior.ColorIndex = xlColorIndexNone Then
・・・・・・・・・・・・・・・・・
 
条件付き書式での塗りつぶしの「白」の判定は
If Range("A3").DisplayFormat.Interior.ColorIndex = 2 Then
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
 
ということになるのでしようか?
 

回答
投稿日時: 20/05/20 22:15:30
投稿者: 半平太

>条件付き書式での塗りつぶしの「白」の判定は
>If Range("A3").DisplayFormat.Interior.ColorIndex = 2 Then
>・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
> ということになるのでしようか?
 
そうなりません。上でコメントしましたが、
 
DisplayFormatは、現在の色を示しているだけで、
その出どころには関知していません。
 
条件付き書式でついた色なのか、
普通についている色なのか区別つかないんですよ。
 

投稿日時: 20/05/21 00:43:14
投稿者: shimoichimabu

takesiさん、半平太さん回答ありがとうございます。
 
>こんだけ見れば違いわかる?
FFFFとか出てきていましたが、理解するのに時間かかりそうです。
 
>DisplayFormatは、現在の色を示しているだけで、その出どころには関知していません。
 
A1〜A3:条件付き書式の塗りつぶしで、黄色・白色・塗りつぶしなし
B1〜B3:通常のの塗りつぶしで、黄色・白色・塗りつぶしなし
 
MsgBox Range("A1").DisplayFormat.Interior.ColorIndex
MsgBox Range("A2").DisplayFormat.Interior.ColorIndex
MsgBox Range("A3").DisplayFormat.Interior.ColorIndex
 
MsgBox Range("B1").DisplayFormat.Interior.ColorIndex
MsgBox Range("B2").DisplayFormat.Interior.ColorIndex
MsgBox Range("B3").DisplayFormat.Interior.ColorIndex
 
メッセージの値はA1=B1、A2=B2,A3=B3 でした。
ご指摘の通りでした。
 
このたび、皆様から貴重な回答・助言を頂き、大変勉強させてもらい、有り難うございました。