【VBA】 Excelシートの書式・条件付き書式を変更できないようにする

困ったこと

• Excelの条件付き書式は、セルをコピーするとルールが増殖する。
• カット＆ペーストすると、書式範囲が飛び飛びの穴開きになってしまう。
• いつの間にか増殖しすぎて、ルール数が1000を超えていることもある。
• そうなってしまうとメンテナンスは不可能。

今回やりたいこと

• Excelでセルをコピーしても条件付き書式が増殖しないように固定化したい。

方針

• ブックを開いたら、対象のシートをまるごとコピーして退避する。
• 保存するタイミングで、退避先シートから書式を復帰する。

コード

'ThisWorkbook に記述する
Option Explicit

'条件付き書式を固定するシートの番号(変更可能)
Const Nシート番号 = 1

'書式退避先のシート名(変更可能)
Const S退避 = "書式退避"

'保存時に書式が復元される
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
	'画面描画を省略して高速化
	Application.ScreenUpdating = False
	'退避シートが存在しなければジャンプ
	On Error GoTo NOT_EXIST
	Sheets(S退避).Visible = False
	'退避シートが存在する場合の処理======
	'退避シートの全セルの書式をコピペ
	Sheets(S退避).Cells.Copy
	Sheets(Nシート番号).Cells.PasteSpecial xlPasteFormats
	Application.CutCopyMode = False
	'画面描画を再開する
	Application.ScreenUpdating = True
	Exit Sub
NOT_EXIST:
	'退避シートが存在しない場合の処理======
	'現在位置を覚えておく
	Dim rg As Range
	Set rg = Selection
	'退避シートをコピーにより作成し、非表示にする
	Sheets(Nシート番号).Copy After:=Sheets(Sheets.Count)
	ActiveSheet.Name = S退避
	ActiveSheet.Visible = False
	'現在位置に戻る
	Application.Goto rg
	'画面描画を再開する
	Application.ScreenUpdating = True
End Sub

使い方

• 上のコードを、ThisWorkbookに記述する。
• １回目の保存時には、退避するシートに書式がコピーされる。
• ２回目の保存時には、退避したシートから書式を復帰してくれる。
• 書式を変更したい場合は、退避したシートを削除してから作業すればよい。

雑感

• 複数人で共用するExcelシートは書式がめちゃくちゃになりやすいので、保存するたびに元の状態に復帰してくれるのは助かる。
• 本来は「条件付き書式」だけを変更できなくするつもりだったのに、書式全体を固定化する方法になってしまった。
• まあ、条件付き書式があることに気づかず、手動で色を塗ろうとするメンバーもいると思うので、それを防ぐのにもいいかもしれない。
• 書式を固定化したいシートが２つ以上ある場合については、今度また考えることにする。