スピル範囲の自動色付け（強調表示）
このVBAコードは、ワークシート上で計算が発生するたび、数式の結果が複数のセルに展開される「スピル」機能が使われているセルを検出し、そのスピル先の全てのセル範囲に特定の条件付き書式を適用します。
これにより、どのセルが動的なスピル結果であるかを視覚的に分かりやすくします。
スピル範囲の自動色付け（強調表示）するVBA
Private Sub Worksheet_Calculate()
On Error Resume Next
Application.ScreenUpdating = False
Const AutoRuleFormula = "=N(""スピル自動書式"")=0" '常に「TRUE」
Const AutoRuleColor = &HFFFFCC '水色：RGB(204, 255, 255)
'前回の条件付書式を削除
Dim CurrentRule As FormatCondition
For Each CurrentRule In Me.Cells.FormatConditions
If CurrentRule.Type = xlExpression And CurrentRule.Formula1 = AutoRuleFormula Then
CurrentRule.Delete
End If
Next CurrentRule
'数式の全セルを取得
Dim AllFormulas As Range
Set AllFormulas = Me.UsedRange.SpecialCells(xlCellTypeFormulas)
'現在のスピル範囲に条件付書式を設定
Dim rng As Range
If Not AllFormulas Is Nothing Then
For Each rng In AllFormulas.Cells
' スピル数式のセルのみを対象
If rng.HasSpill = True Then
With rng.SpillingToRange.FormatConditions.Add(Type:=xlExpression, Formula1:=AutoRuleFormula)
.Interior.Color = AutoRuleColor
End With
End If
Next
End If
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
スピル範囲の自動色付け（強調表示）するVBAの解説
このVBAコードは、Private Sub Worksheet_Calculate()というイベントプロシージャ内に記述されています。
これは、「ワークシート上の計算結果が変更されたとき」にExcelが自動で実行する仕組みです。
具体的にはセルの値が変更されたり、数式の結果が更新されたりして、シートの再計算が発生するたびに、このコードが自動的に呼び出されます。
1. 実行タイミングと準備 (Worksheet_Calculate & ScreenUpdating)
これは、「ワークシート上の計算結果が変更されたとき」にExcelが自動で実行する仕組みです。
具体的にはセルの値が変更されたり、数式の結果が更新されたりして、シートの再計算が発生するたびに、このコードが自動的に呼び出されます。
Private Sub Worksheet_Calculate()
このコードは、ワークシート上の計算結果が変更されたとき（セルに入力や数式の再計算が行われたとき）に自動的に実行されます。
Application.ScreenUpdating = False:
処理中に画面がちらつくのを防ぐため、画面の更新を一時的に停止しています。処理終了時にTrueに戻されます。
このコードは、ワークシート上の計算結果が変更されたとき（セルに入力や数式の再計算が行われたとき）に自動的に実行されます。
処理中に画面がちらつくのを防ぐため、画面の更新を一時的に停止しています。処理終了時にTrueに戻されます。
2. 条件付き書式の定義
Const AutoRuleFormula = "=N(""スピル自動書式"")=0"
これは条件付き書式のための「常に真（TRUE）」になる数式を定義しています。
N()関数は引数を数値に変換し、テキストの場合は0を返すため、0=0で常に「TRUE」となります。
特定のセルを参照せずに、常にTRUEになる式にすることで、セル範囲全体に常に一律で同じ書式を適用するために使用しています。
Const AutoRuleColor = &HFFFFCC
適用する背景色（水色：RGB(204, 255, 255)）の値を定義しています。
これは条件付き書式のための「常に真（TRUE）」になる数式を定義しています。
N()関数は引数を数値に変換し、テキストの場合は0を返すため、0=0で常に「TRUE」となります。
特定のセルを参照せずに、常にTRUEになる式にすることで、セル範囲全体に常に一律で同じ書式を適用するために使用しています。
適用する背景色（水色：RGB(204, 255, 255)）の値を定義しています。
3. 以前の書式の削除（二重適用を防止）
新しい書式を適用する前に、前回の処理で設定した同じ条件付き書式を全て削除します。
For Each CurrentRule In Me.Cells.FormatConditions
シート全体に設定されている条件付き書式を一つずつ確認します。
If CurrentRule.Type = xlExpression And CurrentRule.Formula1 = AutoRuleFormula
タイプが「数式」（xlExpression）であり、かつ、このマクロが自動で設定した特定の数式（AutoRuleFormula）である場合にのみ、その書式をCurrentRule.Deleteで削除します。
シート全体に設定されている条件付き書式を一つずつ確認します。
タイプが「数式」（xlExpression）であり、かつ、このマクロが自動で設定した特定の数式（AutoRuleFormula）である場合にのみ、その書式をCurrentRule.Deleteで削除します。
4. スピル範囲の検出と書式の適用
実際にスピル機能が使われている表を検出し、書式を設定します。
Set AllFormulas = Me.UsedRange.SpecialCells(xlCellTypeFormulas)
シートのデータ範囲（UsedRange）内から、数式が入力されているセルだけをすべて抽出します。
If rng.HasSpill = True
抽出した数式セル（rng）が、スピル機能を持っている（結果が複数のセルに展開されている）かを確認します。
rng.SpillingToRange
スピル機能を持っている場合、このプロパティで数式の結果が展開されている全てのセル範囲（スピル範囲全体）を取得します。
.FormatConditions.Add(...)
取得したスピル範囲全体に対して、定義したAutoRuleFormula（常にTRUE）とAutoRuleColor（水色）の条件付き書式を新しく設定します。
シートのデータ範囲（UsedRange）内から、数式が入力されているセルだけをすべて抽出します。
抽出した数式セル（rng）が、スピル機能を持っている（結果が複数のセルに展開されている）かを確認します。
スピル機能を持っている場合、このプロパティで数式の結果が展開されている全てのセル範囲（スピル範囲全体）を取得します。
取得したスピル範囲全体に対して、定義したAutoRuleFormula（常にTRUE）とAutoRuleColor（水色）の条件付き書式を新しく設定します。
Grokが作成したVBA
以下は、Grokにこの問題を出て書いもらったVBAです。参考まで。
Private Sub Worksheet_Calculate()
Dim prevRanges As Collection
Set prevRanges = GetPrevRanges()
If Not prevRanges Is Nothing Then
Dim addr
For Each addr In prevRanges
On Error Resume Next
Range(addr).Interior.ColorIndex = xlNone
Next
End If
Set prevRanges = New Collection
Dim cell As Range
For Each cell In Me.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.HasSpill Then
Dim spillRng As Range
Set spillRng = cell.SpillingToRange
spillRng.Interior.Color = RGB(204, 255, 255)
prevRanges.Add spillRng.Address
End If
Next
SavePrevRanges prevRanges
End Sub
Private Function GetPrevRanges() As Collection
On Error Resume Next
Dim prop As String
prop = ThisWorkbook.CustomDocumentProperties("PrevSpillRanges").Value
If prop = "" Then Exit Function
Set GetPrevRanges = New Collection
Dim addrs: addrs = Split(prop, ",")
Dim i As Integer
For i = 0 To UBound(addrs)
GetPrevRanges.Add addrs(i)
Next
End Function
Private Sub SavePrevRanges(ranges As Collection)
Dim propVal As String
If ranges.Count > 0 Then
Dim i As Integer
For i = 1 To ranges.Count
propVal = propVal & ranges(i) & ","
Next
propVal = Left(propVal, Len(propVal) - 1)
End If
On Error Resume Next
ThisWorkbook.CustomDocumentProperties("PrevSpillRanges").Delete
ThisWorkbook.CustomDocumentProperties.Add "PrevSpillRanges", False, msoPropertyTypeString, propVal
End Sub
サイト内の参考ページ
第125回.Worksheetのイベントプロシージャー
・Worksheetのイベント ・イベントプロシージャー追加のVBE操作 ・Activate：Worksheetのイベント ・BeforeDoubleClick：Worksheetのイベント ・BeforeRightClick：Worksheetのイベント ・Change：Worksheetのイベント ・SelectionChange：Worksheetのイベント ・全てのシートまたは複数のシートに対するイベント
第135回.ジャンプの選択オプション（SpecialCells）・ジャンプの選択オプションとは ・RangeオブジェクトのSpecialCellsメソッド ・SpecialCellsの使用例
第59回.コレクション処理（For Each）・For Each の構文 ・Exit For ・For Each の使用例 ・RangeオブジェクトのFor Each ・For Each サイト内の参考ページ
第91回.条件付き書式（FormatCondition）・FormatConditionsコレクション ・FormatConditionオブジェクト ・条件付き書式のマクロVBA実践例 ・マクロVBAの条件付き書式について
同じテーマ「マクロVBAサンプル集」の記事
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする（非推奨）
シートを削除：不定数のシート名に対応
セル番地でバラバラに指定されたセルの削除
シート内に散在する複数表の縦結合
スピル範囲の自動色付け（強調表示）
新着記事NEW ・・・新着記事一覧を見る
シンギュラリティ前夜：AIは機械語へ回帰するのか｜生成AI活用研究(2026-01-08)
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
2段階の入力規則リスト作成：最新関数対応｜エクセル関数応用(2025-12-24)
IFS関数をVBAで入力するとスピルに関係なく「@」が付く現象について｜VBA技術解説(2025-12-23)
数値を記号の積み上げでグラフ化する（■は10、□は1）｜エクセル練習問題(2025-12-09)
AI時代におけるVBAシステム開発に関する提言｜生成AI活用研究(2025-12-08)
GrokでVBAを作成：条件付書式を退避回復するVBA｜エクセル雑感(2025-12-06)
顧客ごとの時系列データから直前の履歴を取得する｜エクセル雑感(2025-11-28)
ちょっと悩むVBA厳選問題｜エクセル雑感(2025-11-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- スピル範囲の自動色付け（強調表示）
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.