エクセルVBAで条件付書式の色を取得

Question

セルに条件付書式で書式設定してあります。

A1は「値」100以下
A2は「値」500以下
A3は「値」１～１０の間
B1は「数式」で=B1<A1
以下さまざまな数式があります。

条件に一致すると、セルの文字が「赤」になります。

このとき、
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Font.ColorIndex
End Sub
を実行しても、ColorIndexは、赤の「3」ではなく「-4105」と表示されます。
「-4105」は何もフォントの色を指定してないセルでも同じく表示されます。

質問１．条件付書式で、条件が一致して表示されたフォントの色は取得できないのでしょうか？

質問２．-4105とは何でしょうか？

KenKen_SP · Answer

つれない回答で申し訳ないのですが、、

質問1.

条件付書式によるセル書式を取得することはできません。

質問2.

xlColorIndexAutomatic = -4105 です。
ColorIndex　の定数でセルの色の設定における[自動]にあたります。

KenKen_SP · Answer

#1の補足をしておきます。

#1 の回答のとおり、VBA で条件付書式のセル書式を取得することは
できません。

したがって、条件付書式で表示上セルの文字色が変わったとしても

MsgBox Target.Font.ColorIndex

で得られる色番号は、あくまで予めそのセルに設定されている色の
番号であり、条件付書式による表示上の色の番号ではありません。

＞ColorIndexは、赤の「3」ではなく「-4105」と表示されます。

以上の理由で予めセルの文字に色を設定してなければ、ディフォルト
の色番号である

xlColorIndexAutomatic = -4105

が返ります。

Wendy02 · Answer

merlionXXさん、こんにちは。（お久しぶりかな？）

即席で作ったので、細かな部分は検討されていないので恐縮ですが、こういうことかな？

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fc As FormatCondition
 Dim myColor As Long
　If Target.Count > 1 Then Exit Sub
　If Target.FormatConditions.Count = 0 Then Exit Sub
　For Each fc In Target.FormatConditions
　　If Evaluate(fc.Formula1) Then
　　　myColor = fc.Interior.ColorIndex
　　End If
　Next
　If myColor > 0 Then
　　MsgBox "ColorIndex: " & myColor
　Else
　　MsgBox "自動=条件付で色が変わっていません。"
　End If
End Sub

Wendy02 · Answer

二箇所間違えました。（^^;
最初に、Font のカラーを読み落とし、次に、条件付書式の条件の最初にTrueになったものを優先するのを忘れていました。

？If VarType(myColor) = vbLong Then
これは、これでよかったか、ちょっと自信がないので、うまくいかなかったら、ブレークポイントを取って、ちょっと変数の型をみてくださいね。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Dim myColor As Variant
　Dim i As Long
　With Target
　If .Count > 1 Then Exit Sub
　If .FormatConditions.Count = 0 Then Exit Sub
　　For i = .FormatConditions.Count To 1 Step -1
　　　If Evaluate(.FormatConditions(i).Formula1) Then
　　　　myColor = .FormatConditions(i).Font.ColorIndex
　　　End If
　　Next
　　If VarType(myColor) = vbLong Then
　　　MsgBox "ColorIndex: " & myColor
　　　Else
　　　MsgBox "自動=条件付で色が変わっていません。"
　　End If
　End With
End Sub

Wendy02 · Answer

merlionXXさんも、もうそろそろVBAを教える側に入るかもね。

今回のは、あまり高度とは言えないのですが、もし、今回のポイントはどこか、というなら、ここです。

○　If Evaluate(.FormatConditions(i).Formula1) Then

ここに気が付くなら、merlionXXさんは、もう、特に人に聞かなくても、本からでも、VBA掲示板を読んでいるだけでも吸収できると思います。

これは、Range オブジェクトの中に、条件付書式という部分は、FormatConditions というオブジェクト名に収まっています。FormatConditions 「s」がつくのは、いわゆる、コレクションです。コレクションの中に、式が入っていたら、その条件が成立するかどうかをみてあげます。

そのために、Evaluate があるのですが、独特の関数だと思います。ワークシートのあらゆる数式を、VBA上で実現させてしまうものです。WorksheetFunction では、限られてしまいます。

If VarType(myColor) = vbLong Then

次に、私自身、不安が残ったのですが、ある本で、ちょっと読んだことがあったのです。それは、私たちのPCは、32 bit ですから、数値の扱いは、そのままだとLong型になるそうです。null とお書きになっていたので、万が一にも、nullが入っても可能なように、myColor の変数を Variant 型に替えました。

Variant の変数に、数値が入れば、Long型にキャストされてしまう性質を使いました。つまり、何も入らないと、Empty になっていますから、Long型にはなりませんね。

後は、条件付書式って、条件1 > 条件2 >条件3 の順序になっていたと思います。仮に、条件3 で True が成立しても、その上の段階で、True になれば、その上の段階のIndexColor を優先させる、という考え方です。

何か変なところ、疑問がありましたから、ご面倒でもご指摘ください。

KenKen_SP · Answer

＞条件付書式が「発動」したかどうかの判定も無理なのでしょうか？

#1-2 で不可能と書きましたが、、、いやいや、頭から決め付けるのは
良くないですね。不正確な情報で申し訳ありません。

Wendy02 さんが示されたコードのように FormatConditions から条件を
取得して、それを満たしているか調べれば不可能ではなさそうです。
ただ、

If Evaluate(.FormatConditions(i).Formula1) Then ～

は「数式が」に設定されているときには有効ですが、「セルが」の時
にはエラーになりますね。

そこで Wendy02 さんのコードを参考にして私もコードを書いてみたの
ですが、どうもうまくいきません。

条件を満たすか判定するには Type プロパティー で

　・セルの値が
　・数式が

のいずれかを取得し、それを Operator プロパティーの設定された次の
7 パターンのいづれかで判定しなければならないと思います。

　・xlBetween
　・xlEqual
　・xlGreater
　・xlGreaterEqual
　・xlLess
　・xlLessEqual
　・xlNotBetween
　・xlNotEqual

さらに、条件付書式は3つ設定できる、、、

コードを書いてて泣きが入りました(＠＠?)すみません。

今さらこんなこと言うのもなんなのですが、条件付書式で色が変更された
かどうが取得したとして、何に使うのでしょうか?

Wendy02 · Answer

KenKen_SPさんへ

＞「数式が」に設定されているときには有効ですが、「セルが」の時
＞にはエラーになりますね。

「セルが」にする場合は、本日中には無理だと思いますが、少し考えてみます。

KenKen_SP · Answer

＞基準外（答えが赤字で表示される）のセルを誤って選んでも、色が取得
＞できればエラーロジックで排除できるので是非組み込みたかったのです。

本来のご質問とははずれるかもしれませんが、このような意図であれば、
条件付書式の色で分岐させるのではなく、値で条件分岐をさせた方が良い
と思います。

例えば、簡単な例ですが次のような感じです。

IF Target.Value < 101 Then Exit Sub


＞数式でも値でも条件付書式のフォントの色をちゃんと取得できました。

うーーん。

当方は Windows98SE + Excel2002 の環境なのですが、エラー、、というか
誤まったカラーインデックスが返ります。

ひょっとして、私のやり方がマズイですか?

A1：条件付書式の設定

「セルが」「次の値と等しいとき」「5」

とし、A1 に 5 と入力すると 設定した文字色になり、ColorIndex：46　
と表示され、これは正しいのですが、A1 に 1 と入力しても 
ColorIndex：46 と表示されてしまうのです。

原因としては、「セルが」の場合、

Evaluate(.FormatConditions(i).Formula1) は A1セル の入力値に関わら
ず 5 を返しますので、

If 5 Then ～

となるので評価は必ず True （または必ず False) となってしまう。

ですから、「セルが」の場合は、

IF Target.Value = .FormatConditions(i).Formula1 Then

のようにセルの値と比較する必要があると思います。

ここで、#6でコメントしたように、Operator プロパティーで指定された
比較方法で条件分岐させる必要性を感じました。

例えば、Operator プロパティーの値が xlBetween であったなら、少なく
とも Formula1 と　Formula2 の2つのプロパティーを読みとらないと評価
できないですよね。

うーーん、、、

※質問者さまを差し置いて悩んでしまい、誠に申し訳ありません。
　ご迷惑なら、放置してください。

Wendy02 · Answer

merlionXXさん、KenKen-SPさん、ちょっと、夕飯の支度の前に、作ってみました。

（^^;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Dim myColor As Variant
　Dim i As Long, f2 As String
　With Target
　If .Count > 1 Then Exit Sub
　If .FormatConditions.Count = 0 Then Exit Sub
　　For i = .FormatConditions.Count To 1 Step -1
　　　If .FormatConditions(i).Type = 2 Then
　　　　If Evaluate(.FormatConditions(i).Formula1) Then
　　　　　myColor = .FormatConditions(i).Font.ColorIndex
　　　　End If
　　　　Else
　　　　With .FormatConditions(i)
　　　　On Error Resume Next
　　　　f2 = .Formula2
　　　　If Err.Number > 0 Then
　　　　　　f2 = Empty
　　　　　　Err.Clear
　　　　End If
　　　　On Error GoTo 0
　　　　If FormatCndOperand(.Operator, Target.Value, .Formula1, f2) Then
　　　　　myColor = .Font.ColorIndex
　　　　End If
　　　　End With
　　　End If
　　Next
　　If VarType(myColor) = vbLong Then
　　　MsgBox "ColorIndex: " & myColor
　　　Else
　　　MsgBox "自動=条件付で色が変わっていません。"
　　End If
　End With
End Sub

Private Function FormatCndOperand(ByVal myType As Long, _
　　　　　　　　　　　　　　　　　ByVal myValue As Variant, _
　　　　　　　　　　　　　　　　　ByVal myF1 As Variant, _
　　　　　　　　　　　　　　　　　ByVal myF2 As Variant) As Boolean
Dim myOperand As Variant
Dim myFormula As String
'Replace関数があるので、Excel2000 以上,その代用は、Application.Substitute
If Not (IsEmpty(myValue)) Then
　myOperand = Array("and(v1>= f1, v1 <= f2)", _
　　　　　　　　　　 "not(and(v1 >= f1,v1 <= f2))", _
　　　　　　　　　　 "v1 = f1", _
　　　　　　　　　　 "v1 <> f1", _
　　　　　　　　　　 "v1>f1", _
　　　　　　　　　　 "v1 < f1", _
　　　　　　　　　　 "v1>=f1", _
　　　　　　　　　　 "v1<=f1")
　　　　　　　　　　
　myFormula = myOperand(myType - 1)
　myFormula = Replace(myFormula, "v1", myValue)
　myFormula = Replace(myFormula, "f1", myF1)
　If myF2 <> vbNullString Then
　　myFormula = Replace(myFormula, "f2", myF2)
　End If
　FormatCndOperand = Evaluate(myFormula)
　Else
　FormatCndOperand = False
　End If
End Function

なお、パターンとフォントの区別をさせるコードは、大きくなりすぎますから、アドイン型の方がよいかもしれませんが、元の趣旨から離れてしまうような気がますので、ちょっと遠慮したいです。でも、いつも、merlionXXさんのスレッドは、暴走ぎみになりますね。迷惑じゃないかしら？（^^;

KenKen_SP · Answer

merlionXX さん、Wendy02 さん、レスありがとうございました。いやー、#8で放置してくれ、、とか書きましたけど、本当に放置されなくてうれしいです。何度も挫折しかかりましたが、何とかコードを書いてみました。ロジックはほとんど Wendy02 さんから教えていただいたとおりです。#9 で既に解決かと思いますし、蛇足かもしれませんが、結構頑張って書いたので、折角だからアップさせて下さい。 Wendy02さん、さすがでございます(^^) GetCndNum という関数にしてあります。引数に指定されたセルの条件付き書式が True ならば、その条件番号を返します。こうすることのメリットは、例えば今回のような色を取得したといった場合、次のような関数を作成する際に再利用できそうだからです。 Function GetColorIndex(Target As Range) 　　Dim F As Integer 　　F = GetCndNum(Target) 　　If F Then 　　　　GetColorIndex = _ 　　　　Target.FormatConditions(F).Font.ColorIndex'-----(A) 　　Else 　　　　GetColorIndex = 0 '条件付き書式の色だけほしいのであえて 0 　　End If End Function 応用して(A)で読み取るプロパティーを変えると様々な情報が得られます。 '--------------------　以下コード　------------------------------------ '引数で指定したセルにおいて、そこに設定された条件付き書式の '条件を満たすか調べ、該当する最優先の条件番号を返す '条件を満たさばければ0 を返す Function GetCndNum(rngCel As Range) As Integer 　　Dim f0, f1, f2 　　Dim i As Integer 　　Dim flag As Boolean 　　On Error GoTo ErrorHandler 　　GetCndNum = 0 　　If rngCel.FormatConditions.Count = 0 Or _ 　　　 rngCel.Count > 1 Then Exit Function 　　'条件付き書式の条件を満たすか評価　　flag = False 　　f0 = Evaluate(rngCel.Value) 　　For i = rngCel.FormatConditions.Count To 1 Step -1 　　　　With rngCel.FormatConditions(i) 　　　　　　If .Type = 2 Then 　　　　　　　　'2:xlExpression 「式が」の場合　　　　　　　　If Evaluate(.Formula1) Then flag = True 　　　　　　Else 　　　　　　　　'1:xlCellValue　「セルが」の場合　　　　　　　　f1 = Evaluate(.Formula1) 　　　　　　　　Select Case .Operator 　　　　　　　　　　Case xlBetween 　　　　　　　　　　　　f2 = Evaluate(.Formula2) 　　　　　　　　　　　　If f1 > f2 Then Swap f1, f2 　　　　　　　　　　　　If f1 <= f0 And f0 <= f2 Then flag = True 　　　　　　　　　　Case xlEqual 　　　　　　　　　　　　If f1 = f0 Then flag = True 　　　　　　　　　　Case xlGreater 　　　　　　　　　　　　If f1 > f0 Then flag = True 　　　　　　　　　　Case xlGreaterEqual 　　　　　　　　　　　　If f1 >= f0 Then flag = True 　　　　　　　　　　Case xlLess 　　　　　　　　　　　　If f1 < f0 Then flag = True 　　　　　　　　　　Case xlLessEqual 　　　　　　　　　　　　If f1 <= f0 Then flag = True 　　　　　　　　　　Case xlNotBetween 　　　　　　　　　　　　f2 = Evaluate(.Formula2) 　　　　　　　　　　　　If f1 > f2 Then Swap f1, f2 　　　　　　　　　　　　If Not (f1 <= f0 And f0 <= f2) Then flag = True 　　　　　　　　　　Case xlNotEqual 　　　　　　　　　　　　If f1 <> f0 Then flag = True 　　　　　　　　End Select 　　　　　　End If 　　　　End With 　　　　'戻り値セット　　　　If flag Then 　　　　　　GetCndNum = i 　　　　　　Exit For 　　　　End If 　　Next i 　　Exit Function ErrorHandler: 　　Err.Clear End Function '値の入れ替え Private Sub Swap(ByRef f1, ByRef f2) 　　Dim tmp 　　tmp = f1 　　f1 = f2 　　f2 = tmp End Sub

エクセルVBAで条件付書式の色を取得

つれない回答で申し訳ないのですが、、

#1の補足をしておきます。

merlionXXさん、こんにちは。

この回答への補足

二箇所間違えました。

merlionXXさんも、もうそろそろVBAを教える側に入るかもね。

＞条件付書式が「発動」したかどうかの判定も無理なのでしょうか？

KenKen_SPさんへ

この回答への補足

＞基準外（答えが赤字で表示される）のセルを誤って選んでも、色が取得

この回答への補足

merlionXXさん、KenKen-SPさん、ちょっと、夕飯の支度の前に、作ってみました。

merlionXX さん、Wendy02 さん、レスありがとうございました。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング