Creating a Service Schedule and Tracking Timelines using VBA Code

dano2664

New Member
Joined
Feb 9, 2016
Messages
1
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]Row\Col[/TH]
[TH]
A
[/TH]
[TH]
B
[/TH]
[TH]
C
[/TH]
[TH]
D
[/TH]
[TH]
E
[/TH]
[TH]
F
[/TH]
[TH]
G
[/TH]
[TH]
H
[/TH]
[TH]
I
[/TH]
[TH]
J
[/TH]
[TH]
K
[/TH]
[TH]
L
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5
[/TD]
[TD]Ticket
Number
[/TD]
[TD]Originator[/TD]
[TD]
Submission
Date
[/TD]
[TD]
Request
Date
[/TD]
[TD]
Requesting
Manager
[/TD]
[TD]
Requesting Manager
Approval Date
[/TD]
[TD]
Service
Manager
[/TD]
[TD]
Assignment
Date
[/TD]
[TD]
Analyst
[/TD]
[TD]
Analyst
Approval Date
[/TD]
[TD]
Target
Completion Date
[/TD]
[TD]
Actual
Completion Date
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6
[/TD]
[TD]30[/TD]
[TD]Julian Huffman[/TD]
[TD]
02/24/2016​
[/TD]
[TD]
03/02/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
02/27/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
02/28/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
02/28/2016​
[/TD]
[TD]
03/03/2016​
[/TD]
[TD]
03/08/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7
[/TD]
[TD]29[/TD]
[TD]Isabel Snyders[/TD]
[TD]
02/20/2016​
[/TD]
[TD]
03/08/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/24/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/24/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/25/2016​
[/TD]
[TD]
03/08/2016​
[/TD]
[TD]
03/07/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8
[/TD]
[TD]28[/TD]
[TD]Davis Gladwin[/TD]
[TD]
02/20/2016​
[/TD]
[TD]
02/23/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/20/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
02/22/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
02/24/2016​
[/TD]
[TD]
02/26/2016​
[/TD]
[TD]
02/29/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9
[/TD]
[TD]27[/TD]
[TD]Davis Gladwin[/TD]
[TD]
02/16/2016​
[/TD]
[TD]
03/12/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/17/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/17/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/17/2016​
[/TD]
[TD]
03/13/2016​
[/TD]
[TD]
03/13/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10
[/TD]
[TD]26[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
02/14/2016​
[/TD]
[TD]
02/26/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/15/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/16/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
02/17/2016​
[/TD]
[TD]
02/29/2016​
[/TD]
[TD]
03/04/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11
[/TD]
[TD]25[/TD]
[TD]Julian Huffman[/TD]
[TD]
02/11/2016​
[/TD]
[TD]
02/16/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
02/13/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/14/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
02/14/2016​
[/TD]
[TD]
02/18/2016​
[/TD]
[TD]
02/18/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12
[/TD]
[TD]24[/TD]
[TD]Herbert Tillens[/TD]
[TD]
02/09/2016​
[/TD]
[TD]
02/16/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
02/12/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/12/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
02/13/2016​
[/TD]
[TD]
02/27/2016​
[/TD]
[TD]
02/27/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13
[/TD]
[TD]23[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
02/08/2016​
[/TD]
[TD]
02/25/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/12/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/14/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/16/2016​
[/TD]
[TD]
02/20/2016​
[/TD]
[TD]
02/22/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14
[/TD]
[TD]22[/TD]
[TD]Davis Gladwin[/TD]
[TD]
02/06/2016​
[/TD]
[TD]
02/09/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/06/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/06/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/06/2016​
[/TD]
[TD]
02/10/2016​
[/TD]
[TD]
02/17/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15
[/TD]
[TD]21[/TD]
[TD]Isabel Snyders[/TD]
[TD]
02/06/2016​
[/TD]
[TD]
03/02/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
02/07/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
02/08/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
02/09/2016​
[/TD]
[TD]
02/21/2016​
[/TD]
[TD]
02/21/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16
[/TD]
[TD]20[/TD]
[TD]Julian Huffman[/TD]
[TD]
02/05/2016​
[/TD]
[TD]
02/17/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
02/06/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/07/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/07/2016​
[/TD]
[TD]
02/09/2016​
[/TD]
[TD]
02/09/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17
[/TD]
[TD]19[/TD]
[TD]Herbert Tillens[/TD]
[TD]
02/02/2016​
[/TD]
[TD]
02/07/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
02/04/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
02/04/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
02/05/2016​
[/TD]
[TD]
03/01/2016​
[/TD]
[TD]
03/04/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18
[/TD]
[TD]18[/TD]
[TD]Davis Gladwin[/TD]
[TD]
01/22/2016​
[/TD]
[TD]
01/29/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/25/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/27/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
01/29/2016​
[/TD]
[TD]
02/10/2016​
[/TD]
[TD]
02/15/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
19
[/TD]
[TD]17[/TD]
[TD]Isabel Snyders[/TD]
[TD]
01/25/2016​
[/TD]
[TD]
02/11/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/29/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/29/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
01/29/2016​
[/TD]
[TD]
02/02/2016​
[/TD]
[TD]
02/02/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
20
[/TD]
[TD]16[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
01/22/2016​
[/TD]
[TD]
01/25/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/22/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/23/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
01/24/2016​
[/TD]
[TD]
02/07/2016​
[/TD]
[TD]
02/09/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
21
[/TD]
[TD]15[/TD]
[TD]Julian Huffman[/TD]
[TD]
01/22/2016​
[/TD]
[TD]
02/16/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/23/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/24/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
01/24/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
22
[/TD]
[TD]14[/TD]
[TD]Herbert Tillens[/TD]
[TD]
01/20/2016​
[/TD]
[TD]
02/01/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/21/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/21/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
01/22/2016​
[/TD]
[TD]
01/26/2016​
[/TD]
[TD]
01/29/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
23
[/TD]
[TD]13[/TD]
[TD]Davis Gladwin[/TD]
[TD]
01/26/2016​
[/TD]
[TD]
01/31/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/28/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/30/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
02/01/2016​
[/TD]
[TD]
02/13/2016​
[/TD]
[TD]
02/22/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
24
[/TD]
[TD]12[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
01/20/2016​
[/TD]
[TD]
01/27/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/23/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/23/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
01/23/2016​
[/TD]
[TD]
01/25/2016​
[/TD]
[TD]
01/25/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
25
[/TD]
[TD]11[/TD]
[TD]Julian Huffman[/TD]
[TD]
01/20/2016​
[/TD]
[TD]
02/06/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/24/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/25/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
01/26/2016​
[/TD]
[TD]
02/20/2016​
[/TD]
[TD]
02/21/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
26
[/TD]
[TD]10[/TD]
[TD]Davis Gladwin[/TD]
[TD]
01/20/2016​
[/TD]
[TD]
01/23/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/20/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/21/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
01/21/2016​
[/TD]
[TD]
02/02/2016​
[/TD]
[TD]
02/02/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
27
[/TD]
[TD]9[/TD]
[TD]Isabel Snyders[/TD]
[TD]
01/17/2016​
[/TD]
[TD]
02/11/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/18/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/18/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
01/19/2016​
[/TD]
[TD]
01/23/2016​
[/TD]
[TD]
01/23/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
28
[/TD]
[TD]8[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
01/16/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/17/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/19/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
01/21/2016​
[/TD]
[TD]
02/04/2016​
[/TD]
[TD]
02/02/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
29
[/TD]
[TD]7[/TD]
[TD]Davis Gladwin[/TD]
[TD]
01/12/2016​
[/TD]
[TD]
01/17/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/14/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/14/2016​
[/TD]
[TD]Matthew Traves[/TD]
[TD]
01/14/2016​
[/TD]
[TD]
01/18/2016​
[/TD]
[TD]
01/27/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
30
[/TD]
[TD]6[/TD]
[TD]Herbert Tillens[/TD]
[TD]
01/12/2016​
[/TD]
[TD]
01/19/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/15/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/16/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
01/17/2016​
[/TD]
[TD]
01/21/2016​
[/TD]
[TD]
01/22/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
31
[/TD]
[TD]5[/TD]
[TD]Julian Huffman[/TD]
[TD]
01/11/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/15/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/16/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
01/16/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[TD]
01/28/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
32
[/TD]
[TD]4[/TD]
[TD]Herbert Tillens[/TD]
[TD]
01/07/2016​
[/TD]
[TD]
01/10/2016​
[/TD]
[TD]Tobias Avery[/TD]
[TD]
01/07/2016​
[/TD]
[TD]Christopher Gabrielson[/TD]
[TD]
01/07/2016​
[/TD]
[TD]Christy Varley[/TD]
[TD]
01/08/2016​
[/TD]
[TD]
01/10/2016​
[/TD]
[TD]
01/12/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
33
[/TD]
[TD]3[/TD]
[TD]Isabel Snyders[/TD]
[TD]
01/04/2016​
[/TD]
[TD]
01/29/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/05/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/07/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
01/09/2016​
[/TD]
[TD]
02/03/2016​
[/TD]
[TD]
02/03/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
34
[/TD]
[TD]2[/TD]
[TD]Zane Chamberlain[/TD]
[TD]
01/04/2016​
[/TD]
[TD]
01/16/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/05/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/05/2016​
[/TD]
[TD]Leonard Reed[/TD]
[TD]
01/05/2016​
[/TD]
[TD]
01/17/2016​
[/TD]
[TD]
01/20/2016​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
35
[/TD]
[TD]1[/TD]
[TD]Davis Gladwin[/TD]
[TD]
01/01/2016​
[/TD]
[TD]
01/06/2016​
[/TD]
[TD]Vance Frost[/TD]
[TD]
01/03/2016​
[/TD]
[TD]Caroline Oliverson[/TD]
[TD]
01/04/2016​
[/TD]
[TD]Trenton Pierce[/TD]
[TD]
01/05/2016​
[/TD]
[TD]
01/09/2016​
[/TD]
[TD]
01/09/2016​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: RawData[/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top