Select case target.address named range

ginobuonpanino · Jun 16th 2018

Hi, I want to use select case with a named range. the code I'm using is:

Code

Sub Worksheet_SelectionChange(ByVal Target As range)


Dim i As Integer
Dim m As Integer
Dim n As Integer


Select Case Target.Address


    'Case "$A$1"
        'MsgBox "stop"
        'Exit Sub
'not used by now


    Case Is = range("named_range1")
        MsgBox "it works"
        m = Selection.Row
        n = Selection.Column
        For n = n + 1 To n + 4
            With Cells(m, n)
                .Interior.Color = RGB(255, 255, 0)
                .Value = True
                .Font.Color = RGB(255, 255, 0)
            End With
            With Cells(m + 1, n)
                .Interior.Color = RGB(255, 255, 0)
                .Value = True
                .Font.Color = RGB(255, 255, 0)
            End With
        Next n
    Case Is = range("named_range2")
       msgbox "it works2"
        m = Selection.Row
        n = Selection.Column
        For n = n + 1 To n + 4
            With Cells(m, n)
                .Interior.Color = RGB(146, 208, 80)
                .Value = False
                .Font.Color = RGB(146, 208, 80)
            End With
            With Cells(m - 1, n)
                .Interior.Color = RGB(146, 208, 80)
                .Value = False
                .Font.Color = RGB(146, 208, 80)
            End With
        Next n


    'Case "$H$2"
        'Set ws = Worksheets("Sheet1")
        'Call VBAProject.Module1.test(ws)
'not used by now, but if the code works I will use it


End Select
End Sub

Display More

Now nothing happened, I prefer to not use the if form as usual. The ranges are composed of cells every three rows and every 7 columns, like A1 A8 A4 A8...

thanks for you help!

**Kenneth Hobson** · Jun 17th 2018

If your select case is a string, then your case comparison test should be a string. e.g.

Code

Case Is = range("named_range1").Address

ginobuonpanino · Jun 18th 2018

I don't understand. The select case is target.address. Not a string. So how I have to declare the select case statement?

**pike** · Jun 18th 2018

Hello,
or maybe something like,,,

Code

Option Explicit
Sub Worksheet_SelectionChange(ByVal Target As Range)




    Select Case True


    Case Not Intersect(Target, Range("named_range1")) Is Nothing
        MsgBox "it works"


    Case Not Intersect(Target, Range("named_range2")) Is Nothing
        MsgBox "it works2"




    End Select
End Sub

Display More

**Kenneth Hobson** · Jun 18th 2018

I did not say that your Select Case was not a string. I showed that your Case Is was not.

If you want to test if Target was within a named range, then pike's method is best.

Select case target.address named range

Participate now!

Share