VBA Vlookup

[NickYOW]

I am try to pull required and optional information based on a selected cell. If cell B11 is change from "blank" to "Add" a macro for agentD runs(this I have working)

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell = "Add" Then
Run Cells(7, ActiveCell.Column).Value & Range("A" & ActiveCell.Row).Value & "add"
ElseIf ActiveCell = "Remove" Then
Run Cells(7, ActiveCell.Column).Value & Range("A" & ActiveCell.Row).Value & "remove"
Else
End If
End Sub

then then runs a macro
Sub AgentDadd()
If Range("B11").Value = "Add" Then
Range("B11").Value = "Selected"
Range("B8:B10") = "Reqiuired"
Range("B12:B13") = "Optional"
selected = MsgBox("Do you wish to add the Selected Profile. and Required Profile", vbYesNo, "REQUIED OPTIONS")
If selected = vbYes Then
Range("B8:B11").Value = "Added"
Worksheets("Jobs").Range("B:B").Copy Worksheets("backup").Range("B:B")
Range("B11").Select
extras = MsgBox("Do you want to add the Optional Profile(s)", vbYesNo, "REQUIED OPTIONS")
If extras = vbYes Then
Range("B12:B13") = "Added"
Worksheets("jobs").Range("B:B").Copy Worksheets("backup").Range("B:B")
Range("B11").Select
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B")
Range("B11").Select
End If
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B")
Range("B11").Select
End If
End If
End Sub

I would like to automate this so I don't have to change all my cells should a new task or Position be added. I attempting to have it look at the table array find the active cell from worksheet("Jobs") and then change take the Required cells and change those cell on the job tab from blank to Required and the Optional from blank to Optional on the worksheet("Jobs")

thought I am having trouble doing the vlook up for the validation. and returning the needed if back to the original page.

any assistance would be great.