求助Excel VBA代码：批量粘贴时指定区域单元格变色实现

Got it, let's get this sorted for bulk pastes! Your original code works great for single-cell edits, but it breaks when pasting multiple cells because Target refers to the entire range being changed—you can't directly compare a single oldValue to a multi-cell range's value. Here's the adjusted code that handles both single and bulk changes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchRange As Range
    Dim cell As Range
    Dim oldValue As Variant
    
    ' Set the range you want to monitor (matches your original B2:K20)
    Set watchRange = Me.Range("B2:K20")
    
    ' Only process cells that overlap with our target range
    For Each cell In Intersect(Target, watchRange)
        ' Temporarily disable events to avoid triggering the change event again
        Application.EnableEvents = False
        ' Grab the cell's value right before the change
        oldValue = cell.Value
        ' Re-enable events immediately to not block other actions
        Application.EnableEvents = True
        
        ' Check if the value changed, then color the cell
        If oldValue <> cell.Value Then
            cell.Interior.Color = RGB(184, 245, 198)
        End If
    Next cell
End Sub

Key tweaks that make this work for bulk pastes: #

• Loop through individual cells: Using For Each cell In Intersect(Target, watchRange) ensures we check every cell that's both in the pasted range and your monitored area—no more missing cells in bulk operations.
• Disable events temporarily: When fetching oldValue, we turn off event handling to prevent the Worksheet_Change event from firing again (which would cause an infinite loop). We turn it back on right away so other Excel actions still work normally.
• Universal compatibility: This code handles single-cell edits, bulk pastes, drag-and-drop value changes—any modification to cells in B2:K20 will trigger the color update if the value changed.

Quick notes: #

• Make sure you paste this code into the worksheet module (not a standard module): Right-click the sheet tab > View Code, then paste the code there.
• If you want to reset the cell color when values are changed back to their original state, you'd need to store initial values somewhere (like a hidden sheet or named range), since oldValue only captures the value immediately before the current change.

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