Creating a Service Schedule and Tracking Timelines using VBA Code

[dano2664]

Creating a Service Schedule and Tracking Timelines using VBA Code

Hi all! I am very new to VBA coding and I am trying to create a formatted timeline that tracks important dates for a series of request tickets in a graphical format. I have figured out how to do most of what I'm showing below with nested IF functions in Excel and with conditional formatting. However, I'm trying to automate this for over two thousand legacy tickets going back six years, and allow for the addition of many new tickets each week, and it has become much too cumbersome for Excel to handle.

Does anyone have any tips for how I can make this handled efficiently via VBA code?

My raw data looks like so:
Excel 2010

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
5​	Ticket Number	Originator	Submission Date​	Request Date​	Requesting Manager​	Requesting Manager Approval Date​	Service Manager​	Assignment Date​	Analyst​	Analyst Approval Date​	Target Completion Date​	Actual Completion Date​
6​	30	Julian Huffman	02/24/2016​	03/02/2016​	Tobias Avery	02/27/2016​	Caroline Oliverson	02/28/2016​	Trenton Pierce	02/28/2016​	03/03/2016​	03/08/2016​
7​	29	Isabel Snyders	02/20/2016​	03/08/2016​	Vance Frost	02/24/2016​	Christopher Gabrielson	02/24/2016​	Christy Varley	02/25/2016​	03/08/2016​	03/07/2016​
8​	28	Davis Gladwin	02/20/2016​	02/23/2016​	Vance Frost	02/20/2016​	Caroline Oliverson	02/22/2016​	Leonard Reed	02/24/2016​	02/26/2016​	02/29/2016​
9​	27	Davis Gladwin	02/16/2016​	03/12/2016​	Vance Frost	02/17/2016​	Christopher Gabrielson	02/17/2016​	Christy Varley	02/17/2016​	03/13/2016​	03/13/2016​
10​	26	Zane Chamberlain	02/14/2016​	02/26/2016​	Vance Frost	02/15/2016​	Christopher Gabrielson	02/16/2016​	Matthew Traves	02/17/2016​	02/29/2016​	03/04/2016​
11​	25	Julian Huffman	02/11/2016​	02/16/2016​	Tobias Avery	02/13/2016​	Christopher Gabrielson	02/14/2016​	Matthew Traves	02/14/2016​	02/18/2016​	02/18/2016​
12​	24	Herbert Tillens	02/09/2016​	02/16/2016​	Tobias Avery	02/12/2016​	Christopher Gabrielson	02/12/2016​	Matthew Traves	02/13/2016​	02/27/2016​	02/27/2016​
13​	23	Zane Chamberlain	02/08/2016​	02/25/2016​	Vance Frost	02/12/2016​	Christopher Gabrielson	02/14/2016​	Christy Varley	02/16/2016​	02/20/2016​	02/22/2016​
14​	22	Davis Gladwin	02/06/2016​	02/09/2016​	Vance Frost	02/06/2016​	Christopher Gabrielson	02/06/2016​	Christy Varley	02/06/2016​	02/10/2016​	02/17/2016​
15​	21	Isabel Snyders	02/06/2016​	03/02/2016​	Vance Frost	02/07/2016​	Caroline Oliverson	02/08/2016​	Trenton Pierce	02/09/2016​	02/21/2016​	02/21/2016​
16​	20	Julian Huffman	02/05/2016​	02/17/2016​	Tobias Avery	02/06/2016​	Christopher Gabrielson	02/07/2016​	Christy Varley	02/07/2016​	02/09/2016​	02/09/2016​
17​	19	Herbert Tillens	02/02/2016​	02/07/2016​	Tobias Avery	02/04/2016​	Christopher Gabrielson	02/04/2016​	Matthew Traves	02/05/2016​	03/01/2016​	03/04/2016​
18​	18	Davis Gladwin	01/22/2016​	01/29/2016​	Vance Frost	01/25/2016​	Caroline Oliverson	01/27/2016​	Leonard Reed	01/29/2016​	02/10/2016​	02/15/2016​
19​	17	Isabel Snyders	01/25/2016​	02/11/2016​	Vance Frost	01/29/2016​	Christopher Gabrielson	01/29/2016​	Matthew Traves	01/29/2016​	02/02/2016​	02/02/2016​
20​	16	Zane Chamberlain	01/22/2016​	01/25/2016​	Vance Frost	01/22/2016​	Christopher Gabrielson	01/23/2016​	Christy Varley	01/24/2016​	02/07/2016​	02/09/2016​
21​	15	Julian Huffman	01/22/2016​	02/16/2016​	Tobias Avery	01/23/2016​	Caroline Oliverson	01/24/2016​	Trenton Pierce	01/24/2016​	01/28/2016​	01/28/2016​
22​	14	Herbert Tillens	01/20/2016​	02/01/2016​	Tobias Avery	01/21/2016​	Caroline Oliverson	01/21/2016​	Trenton Pierce	01/22/2016​	01/26/2016​	01/29/2016​
23​	13	Davis Gladwin	01/26/2016​	01/31/2016​	Vance Frost	01/28/2016​	Christopher Gabrielson	01/30/2016​	Christy Varley	02/01/2016​	02/13/2016​	02/22/2016​
24​	12	Zane Chamberlain	01/20/2016​	01/27/2016​	Vance Frost	01/23/2016​	Caroline Oliverson	01/23/2016​	Leonard Reed	01/23/2016​	01/25/2016​	01/25/2016​
25​	11	Julian Huffman	01/20/2016​	02/06/2016​	Tobias Avery	01/24/2016​	Caroline Oliverson	01/25/2016​	Leonard Reed	01/26/2016​	02/20/2016​	02/21/2016​
26​	10	Davis Gladwin	01/20/2016​	01/23/2016​	Vance Frost	01/20/2016​	Christopher Gabrielson	01/21/2016​	Matthew Traves	01/21/2016​	02/02/2016​	02/02/2016​
27​	9	Isabel Snyders	01/17/2016​	02/11/2016​	Vance Frost	01/18/2016​	Christopher Gabrielson	01/18/2016​	Christy Varley	01/19/2016​	01/23/2016​	01/23/2016​
28​	8	Zane Chamberlain	01/16/2016​	01/28/2016​	Vance Frost	01/17/2016​	Christopher Gabrielson	01/19/2016​	Matthew Traves	01/21/2016​	02/04/2016​	02/02/2016​
29​	7	Davis Gladwin	01/12/2016​	01/17/2016​	Vance Frost	01/14/2016​	Christopher Gabrielson	01/14/2016​	Matthew Traves	01/14/2016​	01/18/2016​	01/27/2016​
30​	6	Herbert Tillens	01/12/2016​	01/19/2016​	Tobias Avery	01/15/2016​	Christopher Gabrielson	01/16/2016​	Christy Varley	01/17/2016​	01/21/2016​	01/22/2016​
31​	5	Julian Huffman	01/11/2016​	01/28/2016​	Tobias Avery	01/15/2016​	Caroline Oliverson	01/16/2016​	Trenton Pierce	01/16/2016​	01/28/2016​	01/28/2016​
32​	4	Herbert Tillens	01/07/2016​	01/10/2016​	Tobias Avery	01/07/2016​	Christopher Gabrielson	01/07/2016​	Christy Varley	01/08/2016​	01/10/2016​	01/12/2016​
33​	3	Isabel Snyders	01/04/2016​	01/29/2016​	Vance Frost	01/05/2016​	Caroline Oliverson	01/07/2016​	Leonard Reed	01/09/2016​	02/03/2016​	02/03/2016​
34​	2	Zane Chamberlain	01/04/2016​	01/16/2016​	Vance Frost	01/05/2016​	Caroline Oliverson	01/05/2016​	Leonard Reed	01/05/2016​	01/17/2016​	01/20/2016​
35​	1	Davis Gladwin	01/01/2016​	01/06/2016​	Vance Frost	01/03/2016​	Caroline Oliverson	01/04/2016​	Trenton Pierce	01/05/2016​	01/09/2016​	01/09/2016​

<tbody>
</tbody>

Sheet: RawData

<tbody>
</tbody>

I want to be able to automate the build in VBA so that I achieve the view shown:
https://drive.google.com/file/d/0Bx69YIuFh8I6ekFxUnhxbUZNck0/view?usp=sharing

Here are some of the supporting notes for how I want to populate and format the timeline:

• The fill and font colors in each row should change dependent on the resulting analyst
• For a given row, the cells that are filled with the subject analyst's color should range from the submission date (at the right) to the completion date (at the left)
• If any of the dates are later than the completion date, the cells to the left of the completion date and to the last applicable date should have a patterned fill of the subject color
• Aside from the submission date, which does not need a letter on the timeline, the other six dates of interest should be denoted as follows:
  • M - Requesting manager approval date
  • A - Analyst assignment date
  • Y - Analyst approval date
  • R - Requested completion date (from requester)
  • T - Target completion date (from analyst)
  • C - Actual completion date

My sample Excel file is found here:
https://drive.google.com/open?id=0Bx69YIuFh8I6SzhqRXdhNFVSbzA

The significance of the letters in each column for each day/week is that I'd like to eventually tally the active tickets and also requests, targets, completions, etc. for each week, so I can calculate the percent delivery to request and target, among other things.

Thanks for any help!