[COLOR=#008F00][FONT=Menlo]' This is the main Macro to create and populate the "Tri" worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Sub[/COLOR] CreateAndPopulateTri()[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We stop the screen being updated while the macro is running[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.ScreenUpdating = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the calculation to manual[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.Calculation = xlManual[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to create a new sheet named 'Tri'[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CreateTriSheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Copy the data from the "Original" worksheet to the "Tri" worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CopyData[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Split the QR column into individual QR codes[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] SplitQRCodes[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need the last row from columns A to G to propagate the formula[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Lastrow& = Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]'Create a new column and fill it in with the Alphabetize() formula[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CreateSignature(Lastrow)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Create a newcolumn and fill it in withSignature, PlanEx and Maladie and a counter[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CreateSignaturePlanExMaladie(Lastrow)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Create a newcolumn and fill it in withSignature and Maladie and a counter[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CreateSignatureMaladie(Lastrow)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we can set te calculation back to Automatic[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.Calculation = xlAutomatic[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We extract unique records for columns L and N[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] ExtractUniqueRecords[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We add a filter to the entire table[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("A1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.AutoFilter[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We create a little dashboard[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Call[/COLOR] CreateDashboard[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We resize the columns for readability[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Columns("A:W").EntireColumn.AutoFit[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("A1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We restore the screen being updated while the macro is running[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.ScreenUpdating = [COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function deletes the "Tri" worksheet if it exists and creates a new one.[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CreateTriSheet()[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]If[/COLOR] [COLOR=#011993]Not[/COLOR] GetWorksheet("Tri") [COLOR=#011993]Is[/COLOR] [COLOR=#011993]Nothing[/COLOR] [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.DisplayAlerts = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Worksheets("Tri").Delete[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Application.DisplayAlerts = [COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]If[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Sheets("Original").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]Set[/COLOR] ws = Sheets.Add(After:=ActiveSheet)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] ws.Name = "Tri"[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function copies the data from the "Original" worksheet to the "Tri" worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CopyData()[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo] Sheets("Original").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Columns("A:D").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Sheets("Tri").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] ActiveSheet.Paste[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function splits the QR column into individual QR codes[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] SplitQRCodes()[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' First we create 6 new columns[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Sheets("Tri").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("B:G").EntireColumn.Insert[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we can split the column A into 7 new columns, using 5 characters at a time[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Columns("A:A").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] FieldInfo:=Array(Array(0, 2), Array(5, 2), Array(10, 2), Array(15, 2), Array(20, 2), _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Array(25, 2), Array(30, 2)), TrailingMinusNumbers:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set up the headers to something meaningfull[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("A1").Value = "QR1"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("A1").AutoFill Destination:=Range("A1:G1"), Type:=xlFillDefault[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header row to bold[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Rows(1).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Font.Bold = [COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function creates a new column and populate it with the Alphabetize() formula[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CreateSignature(Lastrow [COLOR=#011993]As[/COLOR] [COLOR=#011993]Long[/COLOR])[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We first create a new column H[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("H:H").EntireColumn.Insert[/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to set the format to "General", otherwise the formula is not parsed[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("H:H").EntireColumn.NumberFormat = "General"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header for the new column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("H1").Value = "Signature"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add the formula for each cell in column H[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("H2:H2").Formula = "=Alphabetize((A2,B2,C2,D2,E2,F2,G2), """")"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("H2").AutoFill Destination:=Range("H2:H" & Lastrow)[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function creates a new column and populate it with the Signature, PlanEx and Maladie concatenation, and then a counter column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CreateSignaturePlanExMaladie(Lastrow [COLOR=#011993]As[/COLOR] [COLOR=#011993]Long[/COLOR])[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to set the format to "General", otherwise the formula is not parsed[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("L:L").EntireColumn.NumberFormat = "General"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header for the new column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("L1").Value = "Signature + PlanEx + Maladie"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add the formula for each cell in column L[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("L2:L2").Formula = "=H2 & ""_"" & K2 & ""_"" & I2"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("L2").AutoFill Destination:=Range("L2:L" & Lastrow)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add a counter column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to set the format to "General", otherwise the formula is not parsed[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("M:M").EntireColumn.NumberFormat = "General"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header for the new column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("M1").Value = "Compteur"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add the formula for each cell in column M[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("M2:M2").Formula = "=COUNTIF(L:L, L2)"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("M2").AutoFill Destination:=Range("M2:M" & Lastrow)[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function creates a new column and populate it with the Signature and Maladie concatenation, and then a counter column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CreateSignatureMaladie(Lastrow [COLOR=#011993]As[/COLOR] [COLOR=#011993]Long[/COLOR])[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to set the format to "General", otherwise the formula is not parsed[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("N:N").EntireColumn.NumberFormat = "General"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header for the new column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("N1").Value = "Signature + Maladie"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add the formula for each cell in column L[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("N2:N2").Formula = "=H2 & ""_"" & I2"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("N2").AutoFill Destination:=Range("N2:N" & Lastrow)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add a counter column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to set the format to "General", otherwise the formula is not parsed[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("O:O").EntireColumn.NumberFormat = "General"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We set the header for the new column[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("O1").Value = "Compteur"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Now we add the formula for each cell in column M[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("O2:O2").Formula = "=COUNTIF(N:N, N2)"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("O2").AutoFill Destination:=Range("O2:O" & Lastrow)[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function creates a custom filter to extract unique records from columns L and N and put them in columns V and W respectively[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] ExtractUniqueRecords()[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo] Columns("L:L").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("V1"), Unique:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Columns("N:N").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("W1"), Unique:=[COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' We need to adjust the columns titles[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("V1").Value = "Signature + PlanEx + Maladie Uniques"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("W1").Value = "Signature + Maladie Uniques"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' This function creates a little dashboard with important numbers[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Function[/COLOR] CreateDashboard()[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Titles[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("R6").Value = "Total Signatures:"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("R7").Value = "% Sign. + PlanEx + Maladie Dupliquées:"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("R8").Value = "% Sign. + Maladie Dupliquées:"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("R6:R8").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Font.Bold = [COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' Formulas[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("S6").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] ActiveCell.Formula = "=COUNTA(H:H) - 1"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("S7").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] ActiveCell.Formula = "=(S6 - COUNTA(V:V) - 1) / S6"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("S8").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] ActiveCell.Formula = "=(S6 - COUNTA(W:W) - 1) / S6"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("S6").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.NumberFormat = "#,##0"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("S7:S8").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.NumberFormat = "0.00%"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo][COLOR=#000000] [/COLOR]' This is just bordering and coloring[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Range("Q5:T9").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Borders(xlDiagonalUp).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]With[/COLOR] Selection.Borders(xlEdgeLeft)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .ColorIndex = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .TintAndShade = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .Weight = xlMedium[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]With[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]With[/COLOR] Selection.Borders(xlEdgeTop)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .ColorIndex = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .TintAndShade = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .Weight = xlMedium[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]With[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]With[/COLOR] Selection.Borders(xlEdgeBottom)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .ColorIndex = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .TintAndShade = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .Weight = xlMedium[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]With[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]With[/COLOR] Selection.Borders(xlEdgeRight)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .ColorIndex = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .TintAndShade = 0[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .Weight = xlMedium[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]With[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Borders(xlInsideVertical).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] Selection.Borders(xlInsideHorizontal).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] [COLOR=#011993]With[/COLOR] Selection.Interior[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .Pattern = xlSolid[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .PatternColorIndex = xlAutomatic[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .ThemeColor = xlThemeColorAccent5[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .TintAndShade = 0.599993896298105[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo] .PatternTintAndShade = 0[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo][COLOR=#000000] [/COLOR]End[COLOR=#000000] [/COLOR]With[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Function[/FONT][/COLOR]