財務モデルの色分けを VBA で自動化する方法

Option Explicit

' 財務モデルの色分け
'   青 = 手入力の数値 / 黒 = 計算式 / 緑 = 他シート参照 / 赤 = 他ファイル参照
' 対象：選択中のシート（複数選択可）の、数値が入ったセルだけ
Sub ColorCodeModel()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWindow.SelectedSheets
        ColorCodeSheet ws
    Next ws
    Application.ScreenUpdating = True
    MsgBox "色分けが終わりました。", vbInformation
End Sub

Private Sub ColorCodeSheet(ws As Worksheet)
    Dim inputs As Range, formulas As Range, c As Range, f As String

    ' 数値の定数と、結果が数値の数式を集める（無ければ Nothing のまま）
    On Error Resume Next
    Set inputs = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    Set formulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
    On Error GoTo 0

    If Not inputs Is Nothing Then inputs.Font.Color = RGB(0, 0, 255)   ' 青

    If formulas Is Nothing Then Exit Sub
    For Each c In formulas.Cells
        f = c.Formula
        If RefersToOtherFile(f) Then
            c.Font.Color = RGB(255, 0, 0)      ' 赤：他ファイル参照
        ElseIf InStr(f, "!") > 0 Then
            c.Font.Color = RGB(0, 128, 0)      ' 緑：他シート参照
        Else
            c.Font.Color = RGB(0, 0, 0)        ' 黒：計算式
        End If
    Next c
End Sub

' 他ファイル参照は「[ブック名]シート名!」の形（保存前のブックは拡張子なし）。
' テーブルの構造化参照「テーブル1[列]」は ] のすぐ後がシート名と ! にならないので区別できる
Private Function RefersToOtherFile(ByVal f As String) As Boolean
    Const STOPS As String = "()[],;+-*/&=<>^"""
    Dim p As Long, i As Long, ch As String
    p = InStr(f, "]")
    Do While p > 0
        For i = p + 1 To Len(f)
            ch = Mid$(f, i, 1)
            If ch = "!" Then
                If i > p + 1 Then RefersToOtherFile = True: Exit Function
                Exit For
            End If
            If InStr(STOPS, ch) > 0 Then Exit For
        Next i
        p = InStr(p + 1, f, "]")
    Loop
End Function