【2025年対応】VBA・マクロで祝日を自動で判定して色分け！エクセルでカレンダーをもっと見やすく

Sub ColorHolidays()
    Dim wsData As Worksheet
    Dim wsHoliday As Worksheet
    Dim checkRange As Range
    Dim cell As Range
    Dim holidayDates As Collection
    Dim i As Long
    Dim lastRow As Long
    Dim hDate As Variant

    Set wsData = ActiveSheet
    Set wsHoliday = Worksheets("祝日一覧")

    ' 祝日データをコレクションに読み込む
    Set holidayDates = New Collection
    lastRow = wsHoliday.Cells(wsHoliday.Rows.Count, 1).End(xlUp).Row

    On Error Resume Next
    For i = 1 To lastRow
        hDate = wsHoliday.Cells(i, 1).Value
        If IsDate(hDate) Then
            holidayDates.Add Format(hDate, "yyyymmdd"), CStr(Format(hDate, "yyyymmdd"))
        End If
    Next i
    On Error GoTo 0

    ' チェック対象（A列）の最終行取得
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    Set checkRange = wsData.Range("A2:A" & lastRow)

    ' 色分け処理
    For Each cell In checkRange
        If IsDate(cell.Value) Then
            Dim dateKey As String
            dateKey = Format(cell.Value, "yyyymmdd")
            On Error Resume Next
            hDate = holidayDates.Item(dateKey)
            If Err.Number = 0 Then
                cell.Interior.Color = RGB(255, 204, 204) ' 祝日：ピンク
            Else
                cell.Interior.ColorIndex = xlNone ' 祝日でなければ色を消す
            End If
            Err.Clear
            On Error GoTo 0
        End If
    Next cell

    MsgBox "祝日のセルに色を付けました！", vbInformation
End Sub