VBA formatting from Array

Sub GenBreakoutRoster()
    Dim memberCount As Integer
    Dim SupName As String, SelectedSupName As String
    Dim SupNameLen As Integer, SelectedSupNameLen As Integer
    Dim xTroop As Integer   'increment the supervisor array
    xTroop = 0

    Dim SupIDKey As String  'This is the place holder for the SupID in the ArraySupRosterBreakout will be SSN
    Dim SupFullName As String    ' This will have the Sup Name and Rank for the heading

    LoadArray.LoadAlphaRosterArray
    Sheet4.Activate
    memberCount = Cells(Rows.count, "A").End(xlUp).Row
    ReDim ArraySupRosterBreakout(memberCount)

    For X = LBound(ArrayMemberData) To UBound(ArrayMemberData)
        SupIDKey = ArrayMemberData(X).SSAN
        SupFullName = ArrayMemberData(X).Grade & " " & ArrayMemberData(X).Full_Name
        SelectedSupName = ArrayMemberData(X).Full_Name
        SelectedSupName = UCase(Replace(SelectedSupName, " ", ""))
        SelectedSupName = Replace(SelectedSupName, ",", "")
        SelectedSupNameLen = Len(SelectedSupName)

        For i = LBound(ArrayMemberData) To UBound(ArrayMemberData)
            SupName = ArrayMemberData(i).Supv_Name
            SupName = UCase(Replace(SupName, " ", ""))
            SupName = Replace(SupName, ",", "")
            SupNameLen = Len(SupName)

            If SupNameLen <> 0 Then
                If Left(SupName, SupNameLen) = Left(SelectedSupName, SupNameLen) Then
                    ArraySupRosterBreakout(xTroop).SupID = SupIDKey
                    ArraySupRosterBreakout(xTroop).SupName = SupFullName
                    ArraySupRosterBreakout(xTroop).TroopRank = ArrayMemberData(i).Grade
                    ArraySupRosterBreakout(xTroop).TroopName = ArrayMemberData(i).Full_Name
                    ArraySupRosterBreakout(xTroop).SupStartDate = ArrayMemberData(i).Supv_Begin_Date
                    ArraySupRosterBreakout(xTroop).NoDaySup = CDate(Date) - CDate(ArrayMemberData(i).Supv_Begin_Date)    ' number of days under current supervision
                    ArraySupRosterBreakout(xTroop).NoDaySupDEROS = CDate(ArrayMemberData(X).DEROS) - CDate(ArrayMemberData(i).Supv_Begin_Date)  ' add the time between SupStartDate and DEROS of SUP
                    ArraySupRosterBreakout(xTroop).EPRCloseout = ArrayMemberData(i).Proj_Eval_Close_Date
                    ArraySupRosterBreakout(xTroop).DaysUntilCloseout = CDate(ArrayMemberData(i).Proj_Eval_Close_Date) - CDate(Date)    'days until closeout
                    ArraySupRosterBreakout(xTroop).DutyTitle = ArrayMemberData(i).Duty_Title
                    ArraySupRosterBreakout(xTroop).SupDEROS = ArrayMemberData(X).DEROS
                    xTroop = xTroop + 1
                End If
            End If
        Next i
    Next X

    'Temp solution for filling the roster to sendout
    Sheet18.Activate
    Dim trigger As Integer    ' tells when the supervisor changes and when to start a new table
    Dim t As Integer    ' ArraySupRosterBreakout indexer
    Dim txCountLocation As Integer
    Dim txCount As Integer    ' number of troops under each sup.
    'this is added to the t count to get the next sup as well.
    X = 1
    txCount = 0
    For t = LBound(ArraySupRosterBreakout) To UBound(ArraySupRosterBreakout)
        'all cells down to first x = x+1 should be Bold font
        Cells(X, 1).Value = ArraySupRosterBreakout(t).SupName    '<-- filled yellow background
        Cells(X, 2).Value = "Number of Troops:"
        txCountLocation = X    ' this will store the location the txCount value will go to add total troop count Cells(X, 3).Value = "XX"    ' ADD THE NUMBER OF TIMES THE TROOPS CYCLE RUNS.
        Cells(X, 5).Value = "DEROS"
        Cells(X, 6).Value = ArraySupRosterBreakout(t).SupDEROS
        'Constant cells headings
        X = X + 1
        ' The below 7 lines need to be bold, filled gray, thick line border all sides
        Cells(X, 1).Value = "Name"
        Cells(X, 2).Value = "Supervision Start Date"
        Cells(X, 3).Value = "# Days Supervised"
        Cells(X, 4).Value = "# Days Supervised at DEROS"
        Cells(X, 5).Value = "EPR Closeout"
        Cells(X, 6).Value = "Days Until Closeout"
        Cells(X, 7).Value = "Duty Title"
        X = X + 1
        'run through each entry to see if supid matches the above sup
        'tx stands for troop from above and x var
        txCount = 0
        For tx = LBound(ArraySupRosterBreakout) To UBound(ArraySupRosterBreakout)
            If ArraySupRosterBreakout(tx).SupID = ArraySupRosterBreakout(t).SupID Then
                'format odd and even for fill no fill, count times this is run for troop count at top of table
                'add a switch if even fill color gray
                'all cells will have border of think line on both sides
                'last cell in list will have border thick line on bottom

                Cells(X, 1).Value = ArraySupRosterBreakout(tx).TroopRank & " " & ArraySupRosterBreakout(tx).TroopName
                Cells(X, 2).Value = ArraySupRosterBreakout(tx).SupStartDate
                Cells(X, 3).Value = ArraySupRosterBreakout(tx).NoDaySup
                Cells(X, 4).Value = ArraySupRosterBreakout(tx).NoDaySupDEROS
                Cells(X, 5).Value = ArraySupRosterBreakout(tx).EPRCloseout
                Cells(X, 6).Value = ArraySupRosterBreakout(tx).DaysUntilCloseout
                Cells(X, 7).Value = ArraySupRosterBreakout(tx).DutyTitle
                X = X + 1
                txCount = txCount + 1
            End If
        Next tx
        Cells(txCountLocation, 3).Value = txCount


        X = X + 1
        t = t + txCount
    Next t
End Sub

Select allOpen in new window