VBAでExcelに条件付き書式ルールを適用する

Excelに条件付き書式という便利な機能が存在しますが、コピー＆ペーストなどで非常に壊れやすいです。それでも色付け定義などが便利なため多用してしまうのが現実です。

一度崩れてしまった条件付き書式ルールを再設定する方法はないか考えていたところ
VBAで全部クリアした後に新たな条件付き書式ルールを設定すればいいのではないかという結論にたどり着きました。

VBAでは、FormatConditionsコレクションを通じて条件付き書式を操作でき、FormatConditions.addで条件付き書式ルールの追加を行えるようです。

お試しデータ（カレンダの土日に色付け）

こちらのカレンダーで土曜日と日曜日になっている列の背景色を色付けするルールを設定します。
3行目には日付が入っており、表示形式で"aaa"が設定され曜日表示を行ってます。

ソースコード

Sub weekendConditionalFormatting()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rule As FormatCondition

    Set ws = ActiveSheet ' アクティブシートを設定
    Set rng = ws.Range("B2:AE9") ' 対象範囲
    
    ' 既存の条件付き書式をクリア
    rng.FormatConditions.Delete
    
    ' 土曜日または日曜日の場合の条件付き書式を追加
    Set rule = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=OR(WEEKDAY(B$3)=1, WEEKDAY(B$3)=7)")
    With rule
        .Interior.Color = RGB(255, 200, 200)
        .StopIfTrue = False
    End With
    
    ' 条件付き書式ルールをExcelに書き込む
    ' 条件付き書式ルールをExcelのUIに表示させるためのトリック｡
    ' ダミーのルールを追加し､すぐに削除することで､Excelに変更を認識させています｡
    ws.Cells.FormatConditions.Add Type:=xlExpression, Formula1:="=FALSE"
    ws.Cells.FormatConditions(ws.Cells.FormatConditions.Count).Delete
    
    MsgBox "条件付き書式を適用しました。", vbInformation
End Sub

結果

土と日の表の範囲内のみに色付けが適用されました。

Excelの条件付き書式ルールにもしっかりルールが追加されてます。

補足

VBAを扱う場合はxlsmの拡張子で設定する必要があるため、他者へ展開しにくいということもあると思います。もしもxlsxファイルで他者に提供したいといった場合は、別ブックに対して書式ルール追加を行うようにVBAを書き換えると楽になります。