着色を残して条件付き書式を削除するマクロ

条件付き書式による背景色・文字色を通常のセル書式として残し、
その上で条件付き書式を削除する方法を解説します。

「条件付き書式を通常書式に変換する」
と言い換えてもいいですね。

選択範囲に実行するタイプの便利マクロですので、
Excel起動時に裏で開かれる「個人用マクロブック」などに搭載して使ってください。

クイックアクセスツールバーやショートカットキーへの登録が便利です。

ソースコード

Sub 着色を残して条件付き書式を削除する()

    ' 全選択時に固まらないように実行エリアを調整
    Dim 実行エリア As Range
    Set 実行エリア = 選択範囲をUsedRangeで切り取って縮小する
    
    On Error Resume Next ' ←シートの保護などのエラー対策
    
    ' 実行エリア内のすべてのセルをループ
    Dim 着色セル As Range
    For Each 着色セル In 実行エリア.Cells

        ' DisplayFomatと普通のFormatを比較して異なった色になっていれば着色
        If 着色セル.DisplayFormat.Interior.Color <> 着色セル.Interior.Color Then
            着色セル.Interior.Color = 着色セル.DisplayFormat.Interior.Color
        End If
        If 着色セル.DisplayFormat.Font.Color <> 着色セル.Interior.Color Then
            着色セル.Font.Color = 着色セル.DisplayFormat.Font.Color
        End If

    Next
    
    ' 実行エリアの条件付き書式を解除
    実行エリア.FormatConditions.Delete
    
End Sub


' 実行エリアの調整
Function 選択範囲をUsedRangeで切り取って縮小する() As Range
    
    ' 行全体・列全体・シート全体が選択されている場合は、
    ' UsedRangeで切り取った範囲を選択しなおし
    If Selection.Address = Selection.EntireRow.Address _
    Or Selection.Address = Selection.EntireColumn.Address Then
        Set 選択範囲をUsedRangeで切り取って縮小する _
            = Intersect(Selection, ActiveSheet.UsedRange)
        選択範囲をUsedRangeで切り取って縮小する.Select
    
    ' それ以外はそのままSelectionに実行
    Else
        Set 選択範囲をUsedRangeで切り取って縮小する = Selection
    End If

End Function

解説

条件付き書式による書式設定やその条件式は、
通常は「FormatConditionオブジェクト」で調べることになるのですが、

• 条件が該当して実際に着色されているか
• 複数該当した場合はどの条件が優先されているか

を調べるのがかなり面倒です。

しかし今回は「最終的にどんな色になっているか」さえわかればいいため、
「現在の表示色：DisplayFormatプロパティ」を調べて、
それが通常の色と変わっているかを調べることでも目的を果たせます。

DisplayFormatはとにかく今表示されている色を取得できるため、

If 着色セル.DisplayFormat.Interior.Color <> 着色セル.Interior.Color Then

と、DisplayFormat.Interior.ColorとInterior.Colorを比べ、
違っていればそれがほぼ「条件付き書式による着色」だとわかります。

あとはそれを通常書式に代入してあげるだけですね。

その書式設定が終わったのち、
FormatConditions.Deleteで条件付き書式を消して終了です。

冒頭の「選択範囲をUsedRangeで切り取って縮小する」関数は、
Selectionがシート全体だったり、行/列全体だったりした際に、
マクロが固まらないようにSelectionを縮めているだけです。

こういった便利マクロは全選択で実行できると便利なため、
本記事以外の便利マクロでも多用しています。

詳しい解説が読みたい方はこちらをどうぞ
www.limecode.jp

テーブル対策を施した場合

さて上記のコードは単純明快でよかったのですが、
DisplayFormatを着色するのは、条件付き書式だけではありません。

見出しの通り「テーブルの縞模様」も影響してしまいます。

テーブルの書式が通常の書式として設定されてしまわないよう、
そこまでしっかり対策した場合は以下のコードになります。

Sub 着色を残して条件付き書式を削除する()

    ' 全選択時に固まらないように実行エリアを調整
    Dim 実行エリア As Range
    Set 実行エリア = 選択範囲をUsedRangeで切り取って縮小する
    
    On Error Resume Next ' ←シートの保護などのエラー対策
    
    ' 実行エリア内のすべてのセルをループ
    Dim 着色セル As Range
    For Each 着色セル In 実行エリア.Cells

        ' DisplayFomatと普通のFormatを比較して異なった色になっていれば着色
        If 着色セル.DisplayFormat.Interior.Color <> 着色セル.Interior.Color Then
            ' 背景色はテーブルの縞模様によるものかも判定
            If IsDisplayFormatがテーブル背景色(着色セル) = False Then
                着色セル.Interior.Color = 着色セル.DisplayFormat.Interior.Color
            End If
        End If
        If 着色セル.DisplayFormat.Font.Color <> 着色セル.Interior.Color Then
            着色セル.Font.Color = 着色セル.DisplayFormat.Font.Color
        End If

    Next
    
    ' 実行エリアの条件付き書式を解除
    実行エリア.FormatConditions.Delete
    
End Sub

' テーブルの背景色判定
Function IsDisplayFormatがテーブル背景色(判定セル As Range) As Boolean
    
    Dim 対象テーブル As ListObject
    Set 対象テーブル = 判定セル.ListObject
    
    If 対象テーブル Is Nothing Then Exit Function
    
    Dim 表示色 As Long: 表示色 = 判定セル.DisplayFormat.Interior.Color
    
    With 判定セル.Parent.Parent.TableStyles(対象テーブル.TableStyle)
        If .TableStyleElements(xlWholeTable).Interior.Color = 表示色 _
        Or .TableStyleElements(xlRowStripe1).Interior.Color = 表示色 _
        Or .TableStyleElements(xlRowStripe2).Interior.Color = 表示色 Then
            IsDisplayFormatがテーブル背景色 = True
        End If
    End With
    
End Function


' 実行エリアの調整
Function 選択範囲をUsedRangeで切り取って縮小する() As Range
    
    ' 行全体・列全体・シート全体が選択されている場合は、
    ' UsedRangeで切り取った範囲を選択しなおし
    If Selection.Address = Selection.EntireRow.Address _
    Or Selection.Address = Selection.EntireColumn.Address Then
        Set 選択範囲をUsedRangeで切り取って縮小する _
            = Intersect(Selection, ActiveSheet.UsedRange)
        選択範囲をUsedRangeで切り取って縮小する.Select
    
    ' それ以外はそのままSelectionに実行
    Else
        Set 選択範囲をUsedRangeで切り取って縮小する = Selection
    End If

End Function

以上がソースコードです。

マクロ本体のSubプロシージャについては、

If IsDisplayFormatがテーブル背景色(着色セル) = False Then

が加えられているだけで、他に変更はありません。

このFunction 「IsDisplayFormatがテーブル背景色」内で、
セルの表示色がテーブルの縞模様かどうかを判定しています。

このFunctionを解説しだすとテーブルの解説になってしまうので、
本記事では割愛し、注意点だけ記載しますと、
このコードは「行の縞模様かどうか」しか判定しておりません。

見出し行や列の縞模様、第1列の着色など、
テーブルデザインをユーザーが編集している場合の判定まで書くと、
尋常じゃなくコードが長くなります。

また、本当に縞模様によってついている色かを、
行の偶奇判定までして調べてもいません。
（縞模様と同じ色の条件付き書式が設定されているところまで考慮していない）

所詮はただの色なので、この辺のコードがちょうどよいのではないかと思います。
（そもそもテーブルの判定自体、成果が書くコストに見合ってない気もします）

本体30分、例外3時間みたいなことがプログラミングには往々にしておきますので、
細部へのこだわりはほどほどに、あとは適当にカスタマイズしてお使いください。