Excel VBA：如何检测状态列变化并固定更新日期

核心思路 #

直接用TODAY()会导致日期随文件打开自动更新，我们可以通过VBA监听状态列的单元格变更事件，在状态修改时写入静态的当前日期到对应日期列，这样日期只会在状态变动时更新，不会自动变化。

具体实现步骤 #

1. 打开VBA编辑器 #

按下Alt + F11组合键打开VBA编辑器，在左侧工程窗口找到目标工作表（比如Sheet1），双击打开代码编辑区。

2. 粘贴工作表变更事件代码 #

将以下代码粘贴到代码窗口，注意根据你的实际列调整（示例中状态列为B列，日期列为C列，可自行修改）：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义状态列范围，可改成你的实际列，比如Columns("D:D")
    Dim statusCol As Range
    Set statusCol = Me.Columns("B:B")
    
    ' 仅处理状态列内的单元格变更
    If Not Intersect(Target, statusCol) Is Nothing Then
        Application.EnableEvents = False ' 防止触发循环事件
        Dim cell As Range
        For Each cell In Intersect(Target, statusCol)
            ' 日期列为状态列右侧第1列，可通过Offset调整偏移量
            If cell.Value <> "" Then
                cell.Offset(0, 1).Value = Date ' 写入静态当前日期
            Else
                cell.Offset(0, 1).ClearContents ' 状态为空时清空日期
            End If
        Next cell
        Application.EnableEvents = True ' 恢复事件触发
    End If
End Sub

3. 代码说明 #

• Worksheet_Change是工作表自带的单元格变更事件，只要单元格内容被修改就会触发
• Intersect用来判断修改的单元格是否属于状态列，过滤无关操作
• Application.EnableEvents = False能避免写入日期时再次触发变更事件，防止循环执行
• Date函数返回的是静态日期值，写入单元格后不会随文件打开自动更新

可选方案：公式+自定义函数 #

如果你偏好公式配合自定义函数的方式，可按以下操作：

1. 插入模块 #

在VBA编辑器右键点击当前工作簿，选择「插入」→「模块」，粘贴自定义函数代码：

Function DetectChange(rng As Range) As Boolean
    Static prevVal As String
    ' 对比单元格当前值与上次值，判断是否发生变更
    If rng.Value <> prevVal Then
        prevVal = rng.Value
        DetectChange = True
    Else
        DetectChange = False
    End If
End Function

2. 输入日期列公式 #

假设状态列为[@Status]，日期列输入以下公式（替换[@日期列]为实际日期列的表头名称）：

=IF(ISBLANK([@Status]), "", IF(DetectChange([@Status]), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())), [@日期列]))

注：此方案需启用宏，修改状态时会触发函数写入静态日期，避免TODAY()的自动更新问题，但稳定性不如工作表事件方案。

内容的提问来源于stack exchange，提问作者user1257758