Can I change this macro to get its range from a sheet cell or cells?

[tonywatsonhelp]

Hi Everyone

been trying to get this to work but i'm stuck

I want to be able to easily edit the ranges in a vba and if I put the in a sheet it will be easier

what I need is to be able to change this macro from having the ranges in it to getting then from a sheet?

So this is my macro

Private Sub Worksheet_Change(ByVal Target As Range) 'OK
 On Error GoTo Handler
    Dim r As Range
  Set r = Intersect(Target, [AB14:AB214,AG14:AG214,AL14:AL214,AQ14:AQ214,AV14:AV214,BA14:BA214,BF14:BF214,BK14:BK214,BP14:BP214])
  If r Is Nothing Then Exit Sub
  If r.Cells.Count <> 1 Then Exit Sub
  
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    If Target.Offset(0, -5) = "" Then
        MsgBox ("This Task is Unable to be Completed Until Prior Tasks are Completed." & vbLf & vbLf & "Please Review the Previous Task for Completion."), , "Completion out of Sequence!"
Target.ClearContents
 .ScreenUpdating = True
    .EnableEvents = True
Exit Sub
          End If
   End With
  
  
Handler:
 With Application
   .ScreenUpdating = True
    .EnableEvents = True
  End With
End Sub

And this is the line I need changing

"Set r = Intersect(Target, [AB14:AB214,AG14:AG214,AL14:AL214,AQ14:AQ214,AV14:AV214,BA14:BA214,BF14:BF214,BK14:BK214,BP14:BP214])"

I need it to get the "AB14:AB214,AG14:AG214,AL14:AL214,AQ14:AQ214,AV14:AV214,BA14:BA214,BF14:BF214,BK14:BK214,BP14:BP214"

from Sheet Control
Cell A1
or if its easier to break them up cell A1 toA9 with A1 holding "AB14:AB214" etc.

I'm really stuck as the intersect seams to stop how I normally do this and if I take it out it goes wrong, please help if you can


Thanks

Tony

 

[RoryA]

You'd do it like this:

 Set r = Intersect(Target, Range(Sheets("Control").Range("A1").value))

 

[tonywatsonhelp]

Brilliant Rory,
Thank you so much, I think I tried every way but that lol!!!! massive thank you