Linking tasks to Profiles within a Macro

[NickYOW]

I am trying to link a set of task to a Profile, my code is currently working but if I add a new task then the the reference cells such as B8(sweep floor) now becomes B9, thought the correct Macro will get called when I change B9 to add, my code within is no longer working.. as B9 relates to wash floor and B22 is linked to take out garbage, but if i add a line then the task become B10 B23 will take out garbage.

working code to call the macro
If ActiveCell = "Add" Then
Run Cells(2, ActiveCell.Column).Value & Range("A" & ActiveCell.Row).Value & "add"
End IF

working code as long as not new tasks are added.

Sub managersweepadd()

If Range("B8").Value = "Add" Then 'cell under column Manager row sweep floor changes to Add
Range("B8").Value = "Selected" ' active cell for Manager Wash change to selected
Range("B9, "B22") = "Optional" ' additional tasks as an option change from blank to Optional
selected = MsgBox("Do you wish to add the Selected jobs.", vbYesNo, "REQUIED OPTIONS")
If selected = vbYes Then
Range("B8").Value = "Added" ' change cell from Selected to Added
Worksheets("Jobs").Range("B:B").Copy Worksheets("backup").Range("B:B") ' creates a runing back up
Range("B8").Select ' returns to last active cell
extras = MsgBox("Do you want to add the Optional Jobs(s)", vbYesNo, "REQUIED OPTIONS")
If extras = vbYes Then
Range("B9,B22") = "Added" ' change option task from optional to Added
Worksheets("jobs").Range("B:B").Copy Worksheets("backup").Range("B:B") ' creates a back up
Range("B8").Select ' returns to last active cell
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B") ' if no selected in msg box then return all cell back to original selection but add the first selection
Range("B8").Select
End If
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B") ' if no is selected then return every thing back to original selection
Range("B8").Select
End If
End If

my though is to have another sheet with all tasks and positions and the relationships.

I was thing about a code with a look up function to cross reference second sheet, so what is selected will find what required to the left will display REQUIRED on the master in the correct cells and the optional ones to the right will display OPTIONAL in the corrected cells on the master, this way I can update the sheet as new tasks are introduced and make changes to what might be required or optional for each of the Positions (ie warehouse, cashier, supervisors A/Man Manager).

I need a way to keep it easy to update as new tasked are introduced.

Thanks in advance, any recommendation and/or assistance is much appreciated.

 

[Fluff]

Duplicate to: VBA Vlookup

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.