Nested If Statements with Intersect

[crookesa]

I am watching a number of named ranges on one sheet and want to try make my code a simple as possible.

Each named range I am watching corresponds to a day of the month so it can be up to 31 named ranges.

I have the following working for one range

If Not Intersect(Target, Range("day_1")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(1)
End If

What I was trying to do was

If Not Intersect(Target, Range("day_1")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(1)
Else If Not Intersect(Target, Range("day_2")) Is Nothing Then
        Call ThisWorkbook.update_day_duties(2)
.... and so on
End If

I get the error "Must be first statement on the line".
Is it ok to list the first code snippet 31 times to watch for each named range (even if it doesn't exists, e.g. 28 days in feb) or is that inefficient.

I will have the same code running on 2 other sheets as each sheet represents a different employee grade and has to be separated like that.
Also, should I be looking for other problems regarding error catching or exiting subs, etc.
First time using this intersect function on Worksheet Changes.

 

[Fluff]

In that case, I would stick with the IF, ElseIf.
If each named range was a single cell, then I would have suggested another approach, but it doesn't work if the named range is more than 1 cell.

 

[crookesa]

In that case, I would stick with the IF, ElseIf.
If each named range was a single cell, then I would have suggested another approach, but it doesn't work if the named range is more than 1 cell.

Thanks for the suggestion, maybe I might find it useful elsewhere?

 

[Fluff]

You could use something like

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    
    Set Rng = Union(Range("Day_1"), Range("Day_2"), Range("Day_3"))
    If Not Intersect(Target, Rng) Is Nothing Then
        MsgBox Target.Name.Name & vbLf & Split(Target.Name.Name, "_")(1)
    End If
End Sub

 

[crookesa]

You could use something like

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
   
    Set Rng = Union(Range("Day_1"), Range("Day_2"), Range("Day_3"))
    If Not Intersect(Target, Rng) Is Nothing Then
        MsgBox Target.Name.Name & vbLf & Split(Target.Name.Name, "_")(1)
    End If
End Sub

Thanks I'll keep it in the treasure chest.

 

[Fluff]

You're welcome.