Excel条件付き書式設定をVBAで一括削除【実務で活用できる】

Excelの「条件付き書式」設定セルのCopy&Paste（コピペ）に気をつけていますか？

Excelの「条件付き書式」は自動でセルの背景色を変えたりフォントの色を変えたりなど、すごく便利な機能ですよね。でも、対象セルをコピペすると「条件付き書式」も一緒にコピペされるんです。知らずにやってしまうと、「条件付き書式」の設定ルールがどんどん増えていってしまい、操作が重くなるという問題があります

ちなみに、Microsoftはこの「条件付き書式」の動作を「仕様」として今のところ対応はされないようです。詳しくはこちら：https://support.microsoft.com/ja-jp/help/2537195

解決方法は、不要な「条件付き書式」のルールをクリアして再設定するしかありませんが「条件付き書式」の設定は結構面倒で、この操作を使い慣れていないと本当に大変です

動作が重いExcelファイルの容量を削減しても、なぜか動作が遅いという場合、「条件付き書式」の増殖を疑ってみる必要があります
Excelファイルの容量をなんとか削減しても、再計算を停止させたりしても、なぜか動作が遅い・重いという場合、この「条件付き書式」が動作を遅くしている原因かもしれません
不要な範囲に「条件付き書式」が設定されていたり、コピペや削除等によって既存の「条件付き書式」が複雑怪奇なものになっているということも少なくありません

では今回は、「条件付き書式」設定を何とかしてみたいと思います。ぜひご覧ください(^^)/

この記事は「条件付き書式設定」をVBAで一括削除する方法について解説していきます
そして今後は「条件付き書式」の再設定についても検討しますので、是非ご覧ください

Excelの「条件付き書式」設定について整理してみます

Excelの「条件付き書式」がどこに設定されているのか見えない

どのセルに条件付き書式が適用されているのかわかりづらいとうより、見た目では全くわからないんですよね。条件付き書式を見つけるには、以下の手順で確認しましょう

【手動で「条件付き書式」の設定を見つける手順】
1. Excelのタブ「ホーム」を選択
2. 条件付き書式
3. ルールの管理(R)
4. 書式ルールの表示(S)「現在の選択範囲」を「このワークシート」に変更

誰がいつ設定したのかはわからないような、実際には使われていない設定がいっぱい表示されていませんか？

VBAを使って整理できるかどうか検証

記事を書く前に、Google先生で確認してみたら、増殖してしまった条件付き書式を整理統合するマクロを紹介している記事がいくつかありました

どんなものかと、実際にいくつか試してみましたが、なかなか自分が思ったようにはうまく動作してくれません。思うように動作させるには、かなり複雑な要求に対応させるか個別に必要部分だけで対応するなどが必要なようです。なので欲張るのは止めることにしました

ということで(^^)/

【この記事では次のとおり整理してみました】
1. 現在設定中の「条件付き書式」をVBAで設定をセルに書き出して保存しておきます
2. 書き出した「条件付き書式」設定で、再設定するものだけピックアップして残します
3. 必要に応じて、設定の調整をします（範囲設定などを調整・再設定します）
4. VBAで「条件付き書式」を全て削除する（不明なものも必要なものもすべて）
5. 保存しておいた設定（3で調整したもの）をVBAで再度ブックに反映させます

いっそのことブックのコピーを取ったうえですべて削除してしまい手動で再設定した方が速いのかも(^^;　でも、VBA使いたいからこんな感じで行こうと思います(^^♪

今回のこの記事では「4.」の「条件付き書式」設定を一気に削除してしまうコードを紹介します

スポンサーリンク

Amazonセールで上手に買い物する【必須情報】

Amazonセールで上手に買い物するための「必須情報」です。プライムデーやブラックフライデーなどのビッグセールのほかタイムセールなどをお得に利用できるようにするための情報が満載です。記事内にはAmazonの該当ページへのリンクを豊富に設置

www.kurumico.com

Excelの「条件付き書式」の設定をVBAで一括削除

使用するシートを用意しておきましょう

「条件付き書式」設定を全て削除するコード

・このコードは次の記事で「保存」と「設定」部分を紹介する予定ですので
・それを盛り込んだ内容になっています

'指定ブックの「条件付き書式」設定を全て削除する
Sub DelFormatConditions()
    Dim ws As Worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.ActiveSheet
    Dim vFileName As Variant
    Dim wb As Workbook
    Dim tgsh As Worksheet
    If sh.Range("A7").Value <> "" Then
        vFileName = sh.Range("A7").Value
    Else: vFileName = ""
    End If
    If chkWbOpened(vFileName) = False Then
        vFileName = Application.GetOpenFilename( _
                FileFilter:="Excelワークブック,*.xls?", _
                Title:="ファイルを指定して下さい", _
                MultiSelect:=False) 'true)
        If vFileName = "" Then _
                MsgBox ("未選択のため処理を中止します！"): Exit Sub
        Set wb = Workbooks.Open(vFileName)
    Else: Set wb = Workbooks(Dir(vFileName))
    End If
    For Each ws In wb.Worksheets
        ws.Cells.FormatConditions.Delete
    Next ws
    MsgBox "「" & Dir(vFileName) & "」 の条件付き書式を全て削除しました！"
End Sub

・「9～10行目」で、「sh.Range(“A7”).Value」を調べていますが、ここにブックのフルパスを保存しておく設定にする予定です
・「13行目」の「chkWbOpened(vFileName)」でファイルの存在を確認しています
・「14行目」で指定ファイルがない場合ダイアログからファイルを指定します
・「23～25行目」で開いたブック内のシート数分をループしてすべてのシートの「条件付き書式」設定を削除しています
【削除しているコード】は「24行目」の
　　　「ws.Cells.FormatConditions.Delete」のたった1行だけです(^^)

セル保存パスのブックが開いているかどうか判定する関数

上のコード「13行目」で呼び出している関数「chkWbOpened(vFileName)」がこれです

'セル保存パスのブックがまだ開いているかどうか判定
Function chkWbOpened(tgFilePath As Variant) As Boolean
    On Error Resume Next
    If tgFilePath = "" Then chkWbOpened = False: Exit Function
    'Appendで開いてエラーかどうか判定
    Open tgFilePath For Append As #1
    Close #1
    If Err.Number > 0 Then
        chkWbOpened = True     '開いている
    Else
        chkWbOpened = False    '開いていない
    End If
End Function

・「4行目」でシートに保存したパスが無い（空欄）の場合は「False」を返します
・「6～7行目」でファイルを「Append」追記モードで開いて（すぐに閉じています）
・「Append」の結果がエラーかどうか判定し、「開いていなかった」エラーなら「False」を「開いていた」ならば「True」を返しています
・この関数は、次の記事でも使用する予定です

まとめ（おわりに）

まとめと感想など

【まとめると】
・大量の条件付き書式でExcelの処理が重くなる
・コピぺで条件付き書式もコピーされ増殖する
・削除で条件付き書式が分断されその結果増殖する

【条件付き書式が遅くならないようにするには】
・範囲を決めて設定する（行や列全体で設定しない）
・コピペはせずに値で貼り付ける
・条件はなるべく一つの数式にまとめる

【増殖を防ぐ方法】補足
「ファイル」「オプション」「詳細設定」の編集設定の中の「データ範囲の形式および数式を拡張する」のチェックを外しておけば増殖しないけど
「条件付き書式」を設定していない部分も拡張しなくなってしまいます(-_-;)

・記事内で紹介したコードの実行は、必ずバックアップを取ってから行ってください
・不要な「条件付き書式」設定はこまめにチェックすることをお勧めします
・特に、自分以外の人にファイルを配布する時には必ずチェックして配布しましょう
・また、提供いただいたファイルのチェックもしておけば安心ですね(^^)

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
是非！サンプルファイルをダウンロード出来ますのでそのまま使ってみてください(^^)/


【今後の記事内容はどうしようかなぁ・・・】
・次回記事は「条件付き書式」の保存・設定についてを予定しています
・今までに紹介した記事で使用したコードの改変も検討します
・高速化した「VlookUp関数」のVBAでの活用法を再検討してみたいと思います
・その他「小ネタいろいろ」などなど・・・・・
・今後これらのどれかについて記事にしていきたいと思います。ご期待ください(^^)/

スポンサーリンク

サンプルファイルをダウンロードできます（下記リンク先へ）

記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ

【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。

www.kurumico.com