Excelのテーブル内にかかる条件付き書式がよく壊れるので対策マクロを作成

Excelで、テーブル内の全行にかかる条件付き書式がよく壊れるというか、やたら分裂してしまうことが多い。仕方ないので、そのExcelファイルを起動する度に条件付き書式を今のテーブル行数に合わせてきれいに作り直す、というVBAマクロを作った。

困ったこと
環境
1. アプリケーションソフト
2. 問題のファイルの作り
やったこと
1. 概要
2. 詳細
結果
備考

困ったこと

Excelで、テーブル内の全行（特定の列の場合もあるし全列の場合もある）にかけた条件付き書式が、使っているうちによく壊れるというか崩れるというか、やたら分裂してしまうことが多い。テーブル内でコピーやオートフィルを使うと壊れやすい気がするが、シンプルに使っている場合にも起こっている気がする。

意図せず増殖したり「適用先」範囲や「ルール」がおかしくなった条件付き書式

また、テーブル直下の行に何か入力することにより最下行が自動的に拡張される際、条件付き書式の対象範囲も自動的に拡張されるはずなのだが、なぜかされないみたいなこともしばしばある。

こういうことが起こると、一部の行・列で条件付き書式が意図した通りに動かなかったりして困る。
この問題には長年煩わされているが、最新バージョンの Excel でも改善される気配はない。
でも、そろそろ何とかしたい。

環境

アプリケーションソフト

Microsoft Excel for Microsoft 365 MSO (バージョン 2311 ビルド 16.0.17029.20028) 64 ビット

問題のファイルの作り

今回問題が起きている Excel ブックには、シート［管理簿］の中にテーブル［管理簿］がある。
そのテーブルの範囲内には、条件付き書式が仕掛けてある。その内容は、S列のどこかの行に何でもよいから文字を入力すると、その行の案件は完了したとして行全体が自動的にグレーで塗りつぶされる、というものである。

やったこと

概要

対策として、Excel のマクロでイベントプロシージャを作った。
そのプロシージャの動きとしては、問題のExcelファイルを起動する度に、今回の不具合が起きやすいテーブルにかかる全ての条件付き書式をいったんクリアして、それと同じ条件付き書式をその時のそのテーブルの行数に合わせてきれいに作り直す、というもの。

詳細

問題の Excel ファイルのモジュール “ThisWorkbook” 内に、以下のようなVBAコードを記述した。（メモ用コメント付き）

Option Explicit

'「管理簿」にかかっている条件付き書式をいったんリセットしてから作り直す。条件付き書式壊れを回避するために。 

'このExcelブックが開いたら自動的に実行
Private Sub Workbook_Open()

'各種宣言
Dim ListRow As String
Dim Range1 As Object

'なぜか対象シートをアクティブシートにしないと失敗するので記述
Sheets("管理簿").Activate

'必要なものを変数に入れておく。

'対象のテーブルの行数を数えたものを、変数に入れる。
ListRow = Sheets("管理簿").ListObjects("管理簿").Range.Rows.Count
'条件付き書式と紐づけたい範囲を変数に入れる。
Set Range1 = Sheets("管理簿").Range(Cells(2, 1), Cells(ListRow, 19))

'まずは上記範囲内にある条件付き書式を消す。
Range1.FormatConditions.Delete

'S列に何か入力された行はグレイアウトするという条件付き書式を再セット
With Range1.FormatConditions
.Add Type:=xlExpression, Formula1:="=NOT($S2="""")"
.Item(1).Interior.ColorIndex = 56
.Item(1).StopIfTrue = False
End With

End Sub