• 締切済み

ExcelVBA異なる適用先範囲の条件付き書式取得

お世話になります。ExcelVBA（Excel2007）にて質問となります。 A1:E1セルにに「1」、「2」、「-」、「4」、「5」と入っており、 条件付き書式で以下の条件が登録されている場合、 FormatConditionsコレクションのFormula1で目的の数式を取得出来ません。 条件１：数式「=A1=1」の適用先「=$A$1」 条件２：数式「=A1="-"」の適用先「=$A$1:$E$1」 イミディウィンドウで実行した結果、次のようになります。 ～～～～～～～～～～～～～～～～～～～～～～～～ debug.print range("A1").FormatConditions.Count 2 debug.print range("A1").FormatConditions(1).Formula1 =A1="-" debug.print range("A1").FormatConditions(2).Formula1 =A1="-" ～～～～～～～～～～～～～～～～～～～～～～～～ セルA1を照査対象とした場合、count数は２と得られるのですが、 FormatConditions(1).Formula1で「=A1="-"」が取得されます。 「=A1=1」を取得する方法はどうすればいいでしょうか？ 条件２の適用先を「=$A$1」とした場合は意図した値を取得できました。 ～～～～～～～～～～～～～～～～～～～～～～～～ debug.print range("A1").FormatConditions.Count 2 debug.print range("A1").FormatConditions(1).Formula1 =A1=1 debug.print range("A1").FormatConditions(2).Formula1 =A1="-" ～～～～～～～～～～～～～～～～～～～～～～～～

みんなの回答

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.1

以下は回答ではなくてすみません。 ＝＝＝ 日時も経過し、回答の出現は望みにくいと思われ、質問文を再整理してわかりやすく表現して、目的なども入れて、再質問をしてはどうでしょう。 こちらは締め切ること。 目的とは、なぜ条件付き書式の設定況を調べようということになったのですか。多分書式設定がうまく行かなかったからですか。知識欲からですか。 これを書いておくと、回答者は理解しやすいと思う。 ーー 質問をみて、回答がつけば、小生も勉強しようと思っていましたが、しかしまだ回答がないようですね。めづらしいケースですね。 　私がよくわからなかった点は、ＶＢＡの条件付き書式の「設定」部分のＶＢＡコードは、どう書いたのか、省かれていてよくわからないことです。 普通は質問に書くでしょう。 range("A1").FormatConditionsAdd　以下略 range("A1:E1").FormatConditionsAdd 2つなのかな。 また「セルの値で」（xlCellValue)で済むと思うものを、「数式が」(xlExpression｠を使ったのですか。 ーー 　私は質問の回答候補者（読者）側で再現性（再現できたのか）に疑問を持ちました。 設定時のアクチブセルはどうなっていましたか（今となってはわからないかもしれないが）。普通は指定セル範囲の左上隅アクチブにして式を設定（操作）するが、これが何かの拍子にづれていると思わぬ設定になってしまう、という、シートへ向かって操作で条件付き書式を設定するときの経験（エクセル質問回答時に得た経験があります。 範囲指定したセル範囲の、それ（アクチブセル）以外のセルでは、「式を他セルに複写するときと同じ仕組み」で、セル指定が（番地に＄なしの場合のことですが）変化するのはご存知ですか。意識的にＡ１セルをアクチブにしてＶＢＡコードを書くと当でしょうか。 ーー 条件付き書式は、実用的にはシートに向かっての手操作をした方が早い。マクロの記録を取ってコードを勉強する方法もおすすめ。 ーー 私が本質問に誘発されてやってみたコードを上げます。結局質問で言っていることはわからなかったが、何かお役に立てば。 Sub test01() Range("A1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="1" Range("A1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色 Range("C1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-" Range("C1").FormatConditions(1).Interior.ColorIndex = 6 ' 文字を黄色 End Sub Sub test02() Range("A1:E1").FormatConditions.Delete Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色 End Sub Sub test03() Range("A1:E1").FormatConditions.Delete Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="4" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色 End Sub Sub test04() Range("A1:E1").FormatConditions.Delete End Sub Sub test05() MsgBox Range("a1:E1").FormatConditions.Count MsgBox xlCellValue MsgBox xlEqual For Each cl In Range("a1:E1") 'cl.FormatConditions.Count MsgBox cl.FormatConditions(1).Type & " " & cl.FormatConditions(1).Operator & " " & cl.FormatConditions(1).Formula1 'MsgBox cl.FormatConditions(2).Type & " " & cl.FormatConditions(2).Operator & " " & cl.FormatConditions(2).Formula1 Next End Sub Sub test06() Range("A1:E1").FormatConditions.Delete Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C[0]=4" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色 End Sub Sub test09() 'MsgBox Range("a1:E1").FormatConditions.Count 'MsgBox xlCellValue 'MsgBox xlEqual For Each cl In Range("a1:E1") 'cl.FormatConditions.Count MsgBox cl.FormatConditions(1).Type MsgBox cl.FormatConditions(1).Formula1 MsgBox cl.FormatConditions(2).Type MsgBox cl.FormatConditions(2).Formula1 'MsgBox cl.FormatConditions(2).Type & " " & cl.FormatConditions(2).Operator & " " & cl.FormatConditions(2).Formula1 Next End Sub Sub test10() Range("A1:E1").FormatConditions.Delete Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=4" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色 End Sub Sub test11() Range("A1:E1").FormatConditions.Delete Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 5 ' 文字を赤色 Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=4" Range("A1:E1").FormatConditions(2).Interior.ColorIndex = 6 ' 文字を黄色 '--- Dim fcs As FormatConditions Set fcs = Range("A1:E1").FormatConditions Set fc1 = fcs(1) ' 1 つ目の条件付き書式を取得 Set fc2 = fcs(2) ' ty = fc1.Type ' 条件の種類 MsgBox ty op = fc1.Operator ' 条件 MsgBox op s1 = fc1.Formula1 ' 1 つ目の値 MsgBox s1 's2 = fc.Formula2 ' 2 つ目の値 'MsgBox s2 '---- ty = fc2.Type ' 条件の種類 MsgBox ty op = fc2.Operator ' 条件 MsgBox op s1 = fc2.Formula1 ' 1 つ目の値 MsgBox s1 End Sub Sub test12() Range("A1:E1").FormatConditions.Delete 'Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=" & "-" Range("A1:E1").FormatConditions.Add Type:=xlValue, Formula1:="-" Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を黄色 End Sub ーー test10()あたりが参考になればよいですが。

nede0122
質問者

お礼

回答ありがとうございます。 質問の提示方についてのアドバイス、参考のコード感謝いたします。 >目的なども入れて、再質問をしてはどうでしょう。 >こちらは締め切ること。 ご回答いただいたコードを参照・検証させて頂いたうえ、 詳細は改めて別の質問で上げさせて頂くか今回の質問の補足にてご報告させて頂きます。 （ご回答頂いたコードを検証するお時間を頂いた後に、今回の質問を締め切らせて頂きます） >目的とは、なぜ条件付き書式の設定況を調べようということになったのですか。 目的についてですが、 最終の様式で使用する目的シートへ作成されているデータが、セルの移動により条件付き書式を設定していてもコピーや行列挿入等で崩れてしまうため、条件付き書式をVBAで再設定する事が目的となります。 ただし、以下の現状により制約があります。 (1)条件付き書式で適応しているのはセルの背景色のみであるため、文字色、表示形式、罫線等を適応済みの目的シートから条件付き書式へのシートへの値コピーでは対応できない。 (2)条件付き書式の条件式が整理の都合により修正・変更されるため、VBAによる条件付き書式の作成が出来ない。 よって、実際の様式シートから条件付き書式の設定を取得した設定シートを作成し、別途条件付き書式の設定プロシージャを用いて目的シートへ条件付き書式を再適用する方法を検討しております。 >多分書式設定がうまく行かなかったからですか。知識欲からですか。 上記目的により先ずは設定ではなく設定値の取得を目的としていたため、「取得」部分に概念をおいて検証しておりました。 今回の質問では、1つのセルに異なる適用先の条件付き書式が設定されている場合に 各条件の適用先範囲と条件式を取得することが出来るようになるための「知識欲」も兼ねております。 >私がよくわからなかった点は、ＶＢＡの条件付き書式の「設定」部分のＶＢＡコードは、 >どう書いたのか、省かれていてよくわからないことです。 >普通は質問に書くでしょう。 >range("A1").FormatConditionsAdd　以下略 >range("A1:E1").FormatConditionsAdd >2つなのかな。 取得の際に使用しているコードは次のようにしています。 i = 0 For Each myRng In .Range("A1:E1") 　　i = i + 1 　　Set fcs = myRng.FormatConditions 　　For j = 0 To fcs.Count - 1 　　　　Cells(i, j + 5) = fcs(j + 1).Formula1 '出力先のセルへ条件式の書き出し 　　Next j Next myRng 条件付き書式の「適用先」範囲が不定なため、指定範囲の各セルを調べて条件式を書出す方法でしておりますが、その際にA1セルを調べた際、今回の質問にあるように「A1=1」の場合を取得出来ず、FormatConditions(1)、(2)共に「A1="-"」が取得されてしまいます。 様式シートに対する実際の設定はVBAでは行っておりませんが、取得した内容を基に再設定する場合のコードは以下を想定しています。 　　Range("A1:E1").Select 　　With Selection 　　　　.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=""-""" 　　　　.FormatConditions(.FormatConditions.Count).SetFirstPriority 　　　　.FormatConditions(1).Interior.Color = RGB(255, 255, 0) 　　　　.FormatConditions(1).StopIfTrue = True 　　End With 　　Range("A1").Select 　　With Selection 　　　　.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=1" 　　　　.FormatConditions(.FormatConditions.Count).SetFirstPriority 　　　　.FormatConditions(1).Interior.Color = RGB(0, 255, 0) 　　　　.FormatConditions(1).StopIfTrue = True 　　End With この方法によって取得した物を再設定する際に、各セルアドレスと、そのセルに設定されている条件式内のセルアドレスに相対的なズレが生じてしまうため、うまく再設定出来るかどうかという点のほうが問題になりそうですが。 >また「セルの値で」（xlCellValue)で済むと思うものを、 >「数式が」(xlExpression｠を使ったのですか。 質問では簡易的にA1:E1で取得時に目的の条件式を取得出来なかったことを再現するため簡略化したデータを使用しておりますが、 実データでは 「=OR(AND(LEN(E3)>0,LEN(V3&AI3)=0),AND(LEN(V3)>0,LEN(AI3)>0,JIS(E3)<>JIS(V3),JIS(E3)<>JIS(AI3)))」 のような条件式が設定されている条件付き書式になるため、セルの値比較ではなく、 条件式の指定である「数式が」(xlExpression｠で設定しています。 以下の妥協案も検討しております。 ・セルに適応されている条件付き書式の適用先範囲を統一させる。 　（同じ「適用先」が設定された複数条件の場合、書き出し、再設定はうまく機能しています） ・様式シートをベースにして、値、表示形式、罫線をVBAで転記する方法にする。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • 条件付き書式の計算式と適用先範囲

    通常のExcel操作による条件付き書式も同じなのですが、VBAで書いているときに疑問が出たので、VBAで質問させていただきます。 図のようにA列とB列の値を比較し、A列とB列の値が同じでない場合、つまり「A列－B列」の値が0でないセルに赤い色をつけることにしました。A列が正で、B列が違っている場合色をつける、という考えかたの処理です。 不思議なのは、手動で条件付き書式画面からやっても、下のコードのVBAでやっても「--A」のところのように、比較式を「"=$A2-$B2<>0"」と、2行目で行っているだけ（比較はここだけ）なのです。 しかし実行結果は、A列とB列の値が同じではない行すべてのB列に色がついています。 B列のセルの同じ行のA列と値が異なる場合、赤い色をセルにつけるのが目的なので、結果としてはこのコード（条件付き書式）は正しく動作しています。 質問は、比較式が「--A」のところ＝2行目にしかないのに、なぜ3行目～20行目も同様の効果が効いているのでしょうか。 この挿入している効果は条件付き書式であるため、B列のセル上の値を左の行（A列）と合致させると、ちゃんと赤い色は消えてくれます。 この謎が解けません。 ---------------------- Sub 左列と異なる値は赤に() '条件書式を挿入 With ActiveSheet Range("B2:B20").Select With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2-$B2<>0"　' --A .FormatConditions(1).Interior.Color = RGB(255, 200, 200) End With Range("A1").Select End With End Sub

  • 条件付き書式の条件を取得時の相対参照について

    Range("対象のセル").FormatConditions(1).Formula1で条件付き書式の条件を取得しようとしています。 しかしアクティブなセルの相対参照として評価されてしまい正しい条件を取得できません。 対象のセルにいちいち移動するのも処理時間がかかってしまうし、 すべての計算式を絶対参照に変更するのも面倒です。 なにかよい方法はないでしょうか？ ご教授お願いします。 環境 Windows xp sp2 Excel 2003

  • EXCELVBAであるセルに設定されている条件式書式を取得したいと

    EXCELVBAであるセルに設定されている条件式書式を取得したいと 以下のマクロを組みました。しかし、3つ設定されている条件式のうち 1つの条件が3回表示されるため、困っています。 何が悪いのかご教授ください。 Public Sub FormatConditionsTest() Dim obj As Range Set obj = Cells(108, 10) Dim colFmt As FormatConditions Set colFmt = obj.FormatConditions Dim item As FormatCondition For Each item In colFmt MsgBox (item.Formula1) Next End Sub

  • 単一セルではなく範囲に対して条件付き書式を設定するとき

    条件付き書式について質問です。 A7:M15の範囲を「色範囲」と名前定義しているとして、 その範囲の中で「あ」から始まる文字列の場合、 フォントの色を赤色に設定したいのですが、 １つ１つのセルに対して条件付き書式を設定していくのであれば当然出来きるのはわかります。 Workbooks(book1).Sheets(sheet1).Cells("A7").FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(A7,""あ*"")" .FormatConditions(1).interior.colorIndex = 6 Workbooks(book1).Sheets(sheet1).Cells("A8").FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(A8,""あ*"")" .FormatConditions(1).interior.colorIndex = 6 ・・・ ・・・ ・・・ しかし、 範囲全てに対して下記の設定をしてVBAを起動 Workbooks(book1).Sheets(sheet1).Cells(色範囲).FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(A7,""あ*"")" .FormatConditions(1).interior.colorIndex = 6 エクセル側でA8のセルの条件付き書式をみると、 =COUNTIF(A8,"あ*") と自動で行（列）が変わっているのかわかりません。 要は名前定義をした範囲選択、行番号選択、列番号選択どの場合でも、 左上に表示されたセル座標をCOUNTIFの文字列の引数に設定すればいいのでしょうか。 よろしくお願いいたします。

  • エクセルについてです。マウスで選択した範囲に、条件付き書式設定で80点

    エクセルについてです。マウスで選択した範囲に、条件付き書式設定で80点以上のセルの背景を赤にする、というマクロを作成したいと思います。選択する範囲は毎回異なります。以下のマクロはマクロの記録で作成したものです。このRangeの部分をマウスで選択した範囲に変更したいのですが、どうしたら良いのでしょうか？範囲の取得はマクロを実行して、途中で「マウスで範囲を指定してください」というメッセージを表示させてからにしたいのですが。 Sub 背景を赤() Range("B13:E19").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=80" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With End Sub

  • Excelの条件付き書式の書式の適用先について

    Excelの「条件付き書式」についての質問です。 「条件付き書式」⇒「新しいルール」⇒「数式を利用して，書式設定するセルを決定」 数式の中に「B$2 = 1」 と入力して 適用先に「$B$2:$H$5」と入力しました。 その時に，2行目で「１」がある列の書式が5行目まで変わりました。 また 数式の中に「$B2 = 1」 と入力して 適用先に「$B$2:$H$5」と入力しました。 その時に，2行目で「１」がある列の書式が5行目まで変わりました。 その時，B列で「１」がある行のがH列まで変わりました。 条件付き書式の行全体・列全体が変わる理由はなぜでしょうか？

  • excel2000　条件付書式５つ

    A1～J10に100個の数値があり、 行ごとの１位～５位にそれぞれ書式を あたえます。 マクロの記録機能を使って１位～３位を。 その後別で４位・５位を記録し、くっつけて みました。 つけたい書式は 以下のマクロの通りのセルのパターン・フォントの色です。 以下は記録したものをくっつけてつくったマクロです。 動作しません。 Sub 条件付書式５つ() Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=LARGE($A1:$J1,1)" Selection.FormatConditions(1).Font.ColorIndex = 2 Selection.FormatConditions(1).Interior.ColorIndex = 1 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=LARGE($A1:$J1,2)" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 16 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=LARGE($A1:$J1,3)" Selection.FormatConditions(3).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=LARGE($A1:$J1,4)" Selection.FormatConditions(4).Font.ColorIndex = 2 Selection.FormatConditions(4).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=LARGE($A1:$J1,5)" Selection.FormatConditions(5).Font.ColorIndex = xlAutomatic Selection.FormatConditions(5).Interior.ColorIndex = 38 End Sub 初心者のためどうしたらいいのかまったく わかりません。 どうぞお願いします。

  • エクセルＶＢＡ　条件付き書式

    エクセルＶＢＡのヘルプで出てこないのですが .FormatConditions(.FormatConditions.Count).SetFirstPriority これはどういう意味なのでしょうか？ 条件付き書式の児童取得で取れたコードなのですが 意味がよくわかりません。

  • Excel2003　VBAにて条件付き書式のマクロを書きたいのですが、

    Excel2003　VBAにて条件付き書式のマクロを書きたいのですが、どうも上手くいきません。 1列おき（C列、E列、G列・・・）に条件付き書式を設定し、 条件は、 ・セルの値が”0”より大きい場合はフォント”赤”で表示。 ・セルの値が”0”より小さい場合はフォント”緑”で表示。 としたいと思いマクロを組んでみました。 Sub Color() Dim j, j0 Dim x Worksheets("sheet1").Active Application.ScreenUpdating = False j0 = 3 j = 300 For x = 3 To 100 Step 2 Range(.Cells(j0, x), .Cells(j, x)).Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="0" Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" Selection.FormatConditions(2).Font.ColorIndex = 10 Next Application.ScreenUpdating = True End Sub デバックでステップインしていくと、ここで実行時エラー1004　アプリケーション定義またはオブジェクト定義のエラーです。がでます。 Selection.FormatConditions(1).Font.ColorIndex = 3 -------------------------------------------------------------------------------------- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="0" Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" Selection.FormatConditions(2).Font.ColorIndex = 10 -------------------------------------------------------------------------------------- この間は、マクロの自動記録で書かれてるので間違ってはいないハズなのですが、、、 お知恵を下さい。 よろしくお願いします。

  • 複数の条件付書式

    Ｂ列に1～５までに数字がランダムにはいっています。 条件は ・Ｂ列が空白ならＡ列は無色 ・Ｂ列が１ならＡ列は赤 ・Ｂ列が２ならＡ列は青 ・Ｂ列が３ならＡ列は黄色 ・Ｂ列が４ならＡ列は緑 というのをマクロで行ないたいです。 自分で記録して Sub Macro32() Columns("A:A").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1=""""" Selection.FormatConditions(1).Interior.Pattern = xlNone Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1=1" Selection.FormatConditions(2).Interior.ColorIndex = 38 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1=2" Selection.FormatConditions(3).Interior.ColorIndex = 40 End Sub ４つ目に Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1=3" Selection.FormatConditions(4).Interior.ColorIndex = 32 とするとエラーになります。 3つ以上の条件付書式の書き方を教えてください。 あとこの構文のSelectionはwithで省略できますか？ よろしくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する