top of page

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

 これで増えていた条件付き書式がクリック一つで希望の設定になるようになりました!




さいごに

 今回ご紹介した条件付き書式の整理マクロを、行の増減をトリガにして設定いただくと、そもそも条件付き書式を増やさないような設定も可能です。

 一人で管理しているExcelなら自分さえ気をつけていれば問題ないかもしれませんが、複数人で使用するファイルはどうしても知らぬ間に不要な設定やデータが増えてしまいがちです。

 ちょっとした工夫で計算が一気に早くなることもありますので、記事を通して少しでも業務改善のお手伝いができましたら幸いです!


 また、前任者が作成したマクロが使えなくなってしまった!今まで手動でしていた作業を自動化したい!などなど、何かお困りごとがございましたら、

 マクロの改修や作成も対応しておりますので、ぜひティーマネジメントまでお問い合わせください

Comments


bottom of page