VBA - Changing Roster Vaules

[Bloodywog]

Hi All,

I have posted a problem last year, still have not worked it out. I need this to work asap. I thought I re-post it. The link is below.

http://www.excelforum.com/excel-prog...er-vaules.html

When a employee goes on leave and a relief employee covers them, i would like the code to find the cell data in "Roster" sheet and put it to the relief employee name. The code below does do what I like it but at moment i have "SW" (shift swap) instead of the employee roster shift. How do I find the cell data in employee on leave from roster and and put it to relief employee?

Any help will be appreciated! Not sure if I breached any forum rules, I'm sorry in advance.

[PCI]

Can you give an example of the final result you need using your file attached

[Bloodywog]

Sure, I use the previous attachment as an example.

Employee 1 has taken leave from 20 Aug 2014 until 23 2014. The relief Employee 6 has to come off his line to cover him.

Employee 6 current roster: / O O O
Employee new roster (cover Employee 2): D D N N

In this case, I will need to change before and after roster manually because he can't work N before the D.

Employee 2 has taken leave from 9 Sep 2014 until 20 Sep 2014. The relief Employee 5 has to come off his line to cover him.

Employee 5 current roster: D D N N / O O O D D N N
Employee new roster (cover Employee 2): / O O O D D N N / O O O

If it's confusing, I will attache new spreadsheet with updates.

Thanks.

[PCI]

Try and comment
Modif are mentioned with ' *****

Please Login or Register  to view this content.

[Bloodywog]

PCI,

WOW... works like a dream! Can't thank you enough!

Just 1 more quick question before I mark it SOLVED, to visually tell my staff on roster I have swap lines, I made each team a colour. After the macro been run, is there anyway to automatically format cells to match the team colour (August & September only for example)....? Do I use condtion formatting? I feel there are too many aruments, I may have to use Interior.ColorIndex in VBA.

I might be pushing it, I'm already grateful for your help.

[PCI]

Conditional format can be a good choice: What are the arguments to use and which cells you want to paint?
Perhaps it will better to open a new thread : People looking for inormation could be confusied with the first need.
BTW:
You know you could put formulas to prepare dates and not entering values manually.
Something like Date(WkY,WkM,WkD) where WkY = year(Previous date), WkM = Month(Previous date)+1, WkD = Previous date + 1

[Bloodywog]

Thanks so much for the help PCI!

I will start the new thread, I try few things first.