Using named ranges in a Select Case Statement

[gailb]

I've used named ranges in VBA code with no problem, but for whatever, reason, I can't figure out why a named range won't work with this case statement.

Basically, myRange is referencing C5:C9. I've also named C5:C9 as Rng1_ thru Rng5_. Now E5:E9 are named myOutput1 thru myOutput2.

One change in cell Rng1_, the output of "Yes" is not being displayed in myOutput1.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("myRange")
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        Select Case Target.Address(False, False)
            Case .Range("Rng1_"): .Range("myOutput1").Value2 = "Yes"
            Case .Range("Rng2_"): .Range("myOutput2").Value2 = "Yes"
            Case .Range("Rng3_"): .Range("myOutput3").Value2 = "Yes"
            Case .Range("Rng4_"): .Range("myOutput4").Value2 = "Yes"
            Case .Range("Rng5_"): .Range("myOutput5").Value2 = "Yes"
        End Select
    End With
End Sub

[HansV]

Try using .Range("Rng1_").Address(False, False) etc.

[gailb]

Yes. That did it. Thanks again Hans.