2023年12月14日

Excel 勝手に増える条件付き書式をマクロで撲滅！

更新日：5月20日

はじめに

　今回の記事を担当するのは、ティーマネジメントのゆったです。　長く使用していたり、複数の人が触るExcelファイルがいつの間にかものすごく重くなっている…といった経験はありませんか。もしそのファイルに「条件付き書式」が設定されていましたら、処理速度が遅い原因になっている可能性があります。

　原因と書きましたが、「条件付き書式」を使用することが悪いわけではありません。ただ、行列の挿入やコピーを行った際に分裂してしまったり、設定したつもりのない「条件付き書式」が大量に増えていることがあるのです。

増える条件付き書式

　「条件付き書式」は設定した条件を満たす場合に文字の色などの書式を変更できる便利な機能です。　ですが、条件付き書式が設定されたセルをコピーして挿入したり行を増やしたりすると、下図のように気付かない間に増えていることがあります。例)　点数(B列)が60以上の場合は赤字にする条件付き書式 1. 「B2:B10」の範囲に1つだけ設定しています

2. 行の追加やコピーペーストを行っていると… 　意図せず「条件付き書式」が3つに増えてしまいました

　使っている内にだんだん処理が遅くなってきたファイルがありましたら、まず「条件付き書式」を確認してみてください。

　そして今回ご紹介するのは、そんな条件付き書式をさっと整理するマクロ(VBA)になります！

条件付き書式をマクロで制御するには

　増えた条件付き書式を元に戻すには、①不要な条件付き書式を全て削除し、改めて ②正しい条件付き書式を設定する必要があります。

① シートに設定されている条件付き書式を全て削除する

　現在のシートに設定されている条件付き書式を削除するマクロのサンプルが下記になります。

Sub 条件付き書式削除_全セル()
　Cells.FormatConditions.Delete
End Sub

　シート名を指定したい場合には、「ActiveSheet」を、セル範囲を指定したい場合には「Cells」を書き変えてください。

例) シート名「Sheet1」の「A1:D10」の範囲に設定されている条件付き書式を削除したい場合

Sub 条件付き書式削除_指定範囲()
　Sheets("Sheet1").Range("A1:D10").FormatConditions.Delete
End Sub

② 条件付き書式を設定する

　条件付き書式では細かく書式や罫線を設定できるため、詳しくはMicrosoft公式サイトをご確認ください。　ここでは値によって文字色を変える条件付き書式のサンプルをご紹介したいと思います。

例) シート名「Sheet1」の「A1:D10」の範囲に、

　　セルの値が30以下の場合に、文字色を赤で太字にする条件付き書式を追加したい場合

Sub 条件付き書式追加_60以上赤字()
  With Worksheets("Sheet1").Range("A1:D10")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="30"
    .FormatConditions(1).Font.Color = vbRed
　　.FormatConditions(1).Font.Bold = True
  End With
End Sub

　条件や書式設定は数多くあるため、ごく一部にはなりますが、数値に対してよく使用する設定値をご紹介します。

　※サンプルでは「以下」という条件のため「xlLessEqual」を設定しています

定数	値	内容
xlBetween	1	範囲内
xlNotBetween	2	範囲外
xlEqual	3	等しい
xlNotEqual	4	等しくない
xlGreater	5	より大きい
xlLess	6	より小さい
xlGreaterEqual	7	以上
xlLessEqual	8	以下

　ぜひサンプルを書き変えて色々な条件と書式を試してみてください。

増えた条件付き書式を削除して正しい条件付き書式に戻す

　先述した①②を組み合わせて、正しい条件付き書式になるようにマクロを記述します。

例) シート名「Sheet1」に設定されている条件付き書式を削除し、

　「B2:B14」の範囲に、

　セルの値が60以上の場合に、文字色を赤で太字にする条件付き書式と

　セルの値が40より小さい場合に、背景色を黄にする条件付き書式を

　追加する

Sub 条件付き書式の整理()
　Sheet("Sheet1").Cells.FormatConditions.Delete

  With Worksheets("Sheet1").Range("B2:B14")
　　.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="60"
　　.FormatConditions(1).Font.Color = vbRed
　　.FormatConditions(1).Font.Bold = True
　　.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="40"
　　.FormatConditions(2).Interior.Color = vbYellow
  End With
End Sub