Excel自动刷新数据变化时，如何用VBA实现单元格高亮与弹窗提示？

The Worksheet_Change event only triggers for manual cell edits, not when formula results update from external data refreshes. To detect these changes, use the Worksheet_Calculate event paired with tracking previous values of your target range.

Step-by-Step Code Implementation #

Paste this code into the worksheet module containing your VLOOKUP cells (open the VBA editor with Alt+F11, double-click the target worksheet in the Project Explorer):

Option Explicit
Dim prevValues As Variant ' Stores previous values of the target range

Private Sub Worksheet_Calculate()
    Dim keyRange As Range
    Set keyRange = Me.Range("A10:W200") ' Your target range
    Dim cell As Range
    Dim rowIdx As Long, colIdx As Long
    
    ' Initialize previous values if not set yet
    If IsEmpty(prevValues) Then
        prevValues = keyRange.Value
        Exit Sub
    End If
    
    ' Disable events to avoid recursion during cell updates
    Application.EnableEvents = False
    
    For Each cell In keyRange
        ' Map cell position to array indices (array is 1-based)
        rowIdx = cell.Row - keyRange.Row + 1
        colIdx = cell.Column - keyRange.Column + 1
        
        ' Compare current value to previous value
        If Not IsError(cell.Value) And Not IsError(prevValues(rowIdx, colIdx)) Then
            If cell.Value <> prevValues(rowIdx, colIdx) Then
                ' Highlight changed cell with yellow fill
                cell.Interior.Color = vbYellow
                ' Show notification
                MsgBox "Cell " & cell.Address & " updated to: " & cell.Value, vbInformation, "Value Changed"
                ' Update stored value to current
                prevValues(rowIdx, colIdx) = cell.Value
            End If
        Else
            ' Handle cases where value changed to/from an error
            If Not IsError(cell.Value) Xor Not IsError(prevValues(rowIdx, colIdx)) Then
                cell.Interior.Color = vbYellow
                MsgBox "Cell " & cell.Address & " changed to error: " & cell.Value, vbExclamation, "Value Changed"
                prevValues(rowIdx, colIdx) = cell.Value
            End If
        End If
    Next cell
    
    ' Re-enable events
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Activate()
    ' Reset stored values when the sheet is activated
    Dim keyRange As Range
    Set keyRange = Me.Range("A10:W200")
    prevValues = keyRange.Value
End Sub

Key Features #

• Detects formula updates: Triggers whenever external data refreshes cause VLOOKUP results to change.
• Cell highlighting: Changed cells are filled with yellow for easy visual identification.
• Error handling: Catches cases where a cell's value switches between a valid result and an error.
• Event safety: Disables events during updates to prevent infinite loops.

Notes #

• The prevValues variable resets when Excel closes, which is appropriate for weekly refresh workflows.
• To clear highlighting later, add a simple macro to reset cell fill colors (e.g., cell.Interior.ColorIndex = xlColorIndexNone).

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