How do I assign Named Ranges to Intersect Range for Worksheet Change?

Question

I don't want to use direct cell references in this bit of VBA code, because these three cells of this "form" will shift around. Can't figure a correct way to get Named Ranges on Interset Target Range approach to work.

Googling, I've not had success (oviously), thus ny first post. Tried changing things up, even random wrapping in quotes, with without square brackets, adding .Name. Ugh. It's Friday.

To color the story,

E18 is StartDate
E19 is Term(mo)
E20 is EndDate

FYI, Cell change triggers code to run nicely. The same named ranges work well in the rest-of-the-code.

Expecting
Something like Range("E18:E18, E19:E19, E20:E20") be turned into named ranges instead (which by design are single cell named assignments) in my non-working code snippet below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range([Term_End_Date], [Term_Duration__mo], [Term_Start_Date])) Is Nothing Then Exit Sub
'was If Intersect(Target, Range("E18:E20")) Is Nothing Then Exit Sub    
'<snip> rest works well

Thanks!

Range("named range"). You might need 3 ifs though, not sure if it works with multiple named ranges quite like that. — FAB

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-06-28T19:05:38

Try this

Dim rng As Range

Set rng = Union(Range("Term_End_Date"), Range("Term_Duration__mo"), _
                Range("Term_Start_Date"))

If Not Intersect(Target, rng) Is Nothing Then
    '~~> Do What you want
End If

How do I assign Named Ranges to Intersect Range for Worksheet Change?

1 Answers