大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、条件付き書式を設定する方法について、ご紹介します。条件付き書式の設定は、「FormatConditions」を使うとできます。条件付き書式の中でも、「数式」を使って条件を設定すると、大体の条件を設定することができるので、便利です。具体的なVBAコードを使って解説していきます。

はじめに

この記事では、条件付き書式の設定について、ご紹介します。

条件付き書式の設定は、「FormatConditions」を使うとできます。

「数式」を使った条件の設定を使えば、大体の設定ができるので、「数式」を使って条件を設定する方法を覚えておくと便利です。

条件付き書式を大量に設定する必要がある場合は、VBAで自動化すると業務を時短できます。

では、VBAで条件付き書式を設定する方法について、解説していきます。

この記事で紹介すること

  • 条件付き書式を設定する方法

目次から見たい項目へ移動すると便利ですよ。

目次

エクセルで条件付き書式を設定

まず最初に、エクセルで条件付き書式を設定する方法について、ご紹介します。

5以上は塗りつぶし

セルの値が「5以上」の場合は、セルを「黄色に塗りつぶし」、というのをやってみます。

「5以上は黄色に塗りつぶし」の条件付き書式を設定

「ホーム」タブの「条件付き書式」の「ルール管理」をクリックします。

ルールの管理を開く

「新規ルール」をクリックします。

新規ルールのクリック

「書式ルールの編集」画面が出てきます。

書式ルールの編集

「数式を使用して、書式設定するセルを決定」を選択して、数式を入力します。

書式ルールの編集で数式を入力する

数式は、「=A1>5」と入力します。

これで、セルA1が「5以上」の場合に、条件が成立するということになります。

次は、書式の入力です。

「書式」をクリックします。

条件付き書式の書式をクリックする

「塗りつぶし」タブから、黄色を選択してOKをクリックします。

書式ルールの編集が完了

これで、書式ルールの編集が完了です。

OKを押して、条件付き書式ルールの管理を見てみます。

条件付き書式ルールの管理に新規のルールが追加されている

条件付き書式ルールの管理に、新規のルールが追加されました。

条件付き書式が設定されたか確認

では、セルA1に、「10」を入力してみます。

セルA1に10を入力した結果

「5以上」なので、条件付き書式の条件が成立して、黄色に塗りつぶしされました。

次は、「3」を入力してみます。

セルA1に3を入力した結果

次は、「5以上」ではないので、条件付き書式の条件が成立せず、書式は変化しないです。

というような感じで、エクセルで条件付き書式が設定できます。

これを、VBAでやってみます。

VBAで条件付き書式を設定

VBAで、条件付き書式を設定してみます。

FormatConditionsを使う

条件付き書式の設定は、「FormatConditions」を使います。

条件付き書式は、「数式」を使った設定を使えば、大体のことができるので、「数式」を使った方法のみ解説します。

入力の仕方は、

「セル範囲.FormatConditions.Add Type:=xlExpression, Formula1:=数式」

というように入力します。

条件設定して、オブジェクトに入力する場合は、次のように入力します。

「Set a = セル範囲.FormatConditions.Add(Type:=xlExpression, Formula1:=数式)」

今回は、オブジェクトを使った方法で、ご紹介します。

5以上は塗りつぶし

先ほどと同じように、セルA1に入力された値が、「5以上」のセルを「黄色」に塗りつぶししてみます。

VBAコードは、こんな感じになります。

Sub TEST1()
    
    Dim Con
    
    '条件付き書式を削除
    Range("A1").FormatConditions.Delete
    
    '条件を、5以上に設定して、オブジェクトに格納
    Set Con = Range("A1").FormatConditions.Add(Type:=xlExpression, Formula1:="=A1>=5")
    
    '条件成立で、背景色を「黄色」
    Con.Interior.ColorIndex = 6
    
End Sub

手順は、

  • 条件付き書式を削除
  • 「5以上」の条件を設定して、オブジェクトに格納
  • 条件が成立した場合の、書式を設定

という流れです。

では、実行してみます。

条件付き書式が、設定されているかを確認してみます。

条件付き書式の確認

条件付き書式ルールの管理の画面
書式ルールの編集の画面で設定内容を確認する

先ほどと同じように、数式「=A1>=5」が設定されています。

では、セルA1に「10」を入力してみます。

「10」を入力してみる

セルA1に10を入力した結果

セルA1に入力された値が、「5以上」なので、黄色に塗りつぶしされました。

次は、条件が不成立の場合で、「3」を入力してみます。

「3」を入力してみる

セルA1に3を入力した結果

「3」は、「5以上」ではないので、書式はそのままとなりました。

こんな感じで、VBAで、条件付き書式を設定することができます。

他のパターンもやってみます。

2~5は塗りつぶし

次は、セルA1に入力された値が、「2~5」の場合に、セルを塗りつぶししてみます。

VBAコードは、こんな感じになります。

Sub TEST2()
    
    Dim Con
    
    '条件付き書式を削除
    Range("A1").FormatConditions.Delete
    
    '条件を、2~5に設定して、オブジェクトに格納
    Set Con = Range("A1").FormatConditions.Add(Type:=xlExpression, Formula1:="=And(2<=A1, A1<=5)")
    
    '条件成立で、背景色を「黄色」
    Con.Interior.ColorIndex = 6
    
End Sub

手順は、先ほどと同じで、

  • 条件付き書式を削除
  • 「2~5」の条件を設定して、オブジェクトに格納
  • 条件が成立した場合の、書式を設定

という流れです。

先ほどと違うのが、数式に「"=And(2<=A1, A1<=5)"」と入力しているところです。

条件付き書式は、数式を使用して、条件を設定した方が、簡単です。

では、実行してみます。

条件付き書式が設定されたか確認してみます。

条件付き書式が設定されたか確認

条件付き書式ルールの管理で確認
書式ルールの編集で設定された内容を確認

数式「=AND(2<=A1, A1<=5)」となっていて、「2~5」の場合の条件が設定されています。

条件が成立した場合の書式も、「黄色に塗りつぶし」となっています。

では、セルA1に「2~5」の範囲内である、「3」を入力してみます。

「3」を入力してみる

セルA1に3を入力した結果

「3」は、「2~5」の範囲内の値なので、「黄色」に塗りつぶしされました。

次は、セルA1に「2~5」の範囲外の値である、「10」を入力してみます。

「10」を入力してみる

セルA1に10を入力した結果

「10」は、「2~5」の範囲外の値なので、条件が不成立となり、書式はそのままです。

こんな感じで、数式を使って条件を指定すると、簡単に条件を設定できます。

条件を相対参照にすると便利

条件を相対参照で設定すると、そのまま他のセルでも使えるので便利です。

複数のセルに、条件付き書式を設定するVBAコードです。

「2~5」の値を、「黄色」で塗りつぶす、条件付き書式を設定します。

Sub TEST3()
    
    Dim Con
    
    '条件付き書式を削除
    Range("A1:A10").FormatConditions.Delete
    
    '条件を、2~5以上に設定して、オブジェクトに格納
    Set Con = Range("A1:A10").FormatConditions.Add(Type:=xlExpression, Formula1:="=And(2<=A1, A1<=5)")
    
    '条件成立で、背景色を「黄色」
    Con.Interior.ColorIndex = 6
    
End Sub

条件を設定するセル範囲を、「Range("A1:A10")」としています。

条件を設定する数式は、「"=And(2<=A1, A1<=5)"」として、相対参照にしています。

相対参照は、「$」をつけないで、セルを固定しない方法です。

セルA1~A10に数字を入力しました。

表を用意

セルA1~A10に数字を入力

では、このA1~A10に条件付き書式を設定します。

実行すると、条件付き書式が設定されます。

条件付き書式が設定される

条件付き書式ルールの管理
書式ルールの編集

条件付き書式の結果は、こんな感じになります。

「2~5」の範囲内の値が塗りつぶし

複数のセル範囲に条件付き書式を設定した結果

「2~5」の値が、塗りつぶされています。

絶対参照だとうまくいかない

ちなみに、次のように絶対参照で数式を設定すると、うまくいきません。

Sub TEST4()
    
    Dim Con
    
    '条件付き書式を削除
    Range("A1:A10").FormatConditions.Delete
    
    '条件を、2~5に設定して、オブジェクトに格納
    Set Con = Range("A1:A10").FormatConditions.Add(Type:=xlExpression, Formula1:="=And(2<=$A$1, $A$1<=5)")
    
    '条件成立で、背景色を「黄色」
    Con.Interior.ColorIndex = 6
    
End Sub

数式を、「"=And(2<=$A$1, $A$1<=5)"」と、絶対参照にしました。

実行してみます。

絶対参照で数式を設定した場合は、うまくいかない

絶対参照で、数式を設定した場合は、書式はそのままです。

相対参照であれば、複数のセル範囲でも、同じ数式で条件付き書式を設定することができます。

ちょっと応用

ちょっと応用で、別シートの値を条件として、条件付き書式を設定する、というのをやってみます。

別シートの値を条件とした条件付き書式

「Sheet2」に次のような表を用意しました。

表を用意

表を用意

この表から、別シートの「Sheet1」に値を参照します。

こんな感じです。

別シートにある表の値を参照しておく

別シートの値を参照する

やりたいのは、参照先シートの表から、在庫を確認して、「5未満」の場合は、セルを黄色で塗りつぶすというものです。

VBAコードは、こんな感じになります。

Sub TEST5()
    
    Dim a, Rg, Con
    
    For Each a In Range("A2:A5")
        
        '条件付き書式を削除
        a.FormatConditions.Delete
        
        '参照先のセルで、1つ右のセルのアドレスを取得
        Rg = Range(a.Formula).Offset(0, 1).Address(external:=True)
        
        '条件を、5未満に設定して、オブジェクトに格納
        Set Con = a.FormatConditions.Add(Type:=xlExpression, Formula1:="=" & Rg & "<5")
        
        '条件成立で、背景色を「黄色」
        Con.Interior.ColorIndex = 6
        
    Next
    
End Sub

では、実行します。

条件付き書式が設定される

条件付き書式ルールの管理
書式ルールの編集

条件式に「"=Sheet2!$B$3<5"」が設定されて、別シートの値を参照した条件が設定されています。

シートを見てみます。

在庫が「5未満」の場合、塗りつぶし

別シートの表を参照して条件付き書式を設定した結果

「C」と「D」が塗りつぶしされました。

別シートの値を参照しているセルで、在庫が「5未満」なのは、「C」と「D」なので、正しく判定されています。

こんな感じで、数式をうまく使うと、別シートの値を使って条件式を作ったりもできます。

おわりに 

この記事では、条件付き書式の設定について、ご紹介しました。

条件付き書式の設定は、「FormatConditions」を使うとできます。

「数式」を使った条件の設定を使えば、大体の設定ができるので、「数式」を使って条件を設定する方法を覚えておくと便利です。

条件付き書式を大量に設定する必要がある場合は、VBAで自動化すると業務を時短できます。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す