如何在Worksheet Change事件中用命名区域判断Intersect Range？

Got it, let's get this sorted out for you. The issue with your original attempt is that the Range() method doesn't accept multiple separate ranges as arguments like that—it expects either a single address/name, or a pair of cells to define a continuous range. To check against multiple named ranges, you need to merge them into a single Range object using Application.Union.

Corrected Code #

Here's the fix for your Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim monitoredRange As Range
    
    ' Combine your three named ranges into one single range object
    Set monitoredRange = Application.Union( _
        Range("Term_Start_Date"), _
        Range("Term_Duration__mo"), _
        Range("Term_End_Date") _
    )
    
    ' Check if the changed cell intersects with our monitored range
    If Intersect(Target, monitoredRange) Is Nothing Then Exit Sub
    
    '<snip> 其余代码运行正常
End Sub

Why Your Original Attempt Failed #

When you wrote Range([Term_End_Date], [Term_Duration__mo], [Term_Start_Date]), Excel's VBA interpreter was treating the first two arguments as the top-left and bottom-right corners of a continuous range, completely ignoring the third. This meant it wasn't actually checking against all three of your named ranges—just a random range between two of them.

Alternative (More Concise) Version #

If you prefer to skip the variable and do it in one line, this works too:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Application.Union(Range("Term_Start_Date"), Range("Term_Duration__mo"), Range("Term_End_Date"))) Is Nothing Then Exit Sub
    
    '<snip> 其余代码运行正常
End Sub

Quick Note on Named Range Syntax #

Just to clarify: [Term_Start_Date] is a shorthand for Range("Term_Start_Date") in VBA, so both work. The key was using Union to combine the separate named ranges into a single range that Intersect can evaluate against.

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