Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Const xlUnderlineStyleNone = -4142
- Const xlThemeColorLight1 = 2
- Const xlThemeFontMinor = 2
- Const xlThemeColorLight2 = 4
- Const xlCenter = -4108
- Const xlBottom = -4107
- Const xlContext = -5002
- Const xlNone = -4142
- Const xlEdgeBottom = 9
- Const xlDouble = -4119
- Const xlThick = 4
- Const xlGeneral = 1
- Set xl = CreateObject("Excel.Application")
- xl.Visible = True 'set to False for production
- Set wb = xl.Workbooks.Open("C:\Users\rbelenyesi\Desktop\NNG\WarRoom\as.xlsx")
- ' DailyWarRoom Macro
- ' Format
- wb.Sheets(1).Range("H2:H35").NumberFormat = "m/d/yyyy"
- wb.Sheets(1).Range("S2:S35").NumberFormat = "m/d/yyyy"
- wb.Sheets(1).Range("T2:T33").NumberFormat = "m/d/yyyy"
- wb.Sheets(1).Range("H2:H35").NumberFormat = "m/d/yyyy"
- Application.CutCopyMode = False
- With wb.Sheets("Sheet1").Range("A1:T1").Font
- .Name = "Calibri"
- .Size = 13
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontMinor
- .Bold = True
- .ThemeColor = xlThemeColorLight2
- .TintAndShade = 0
- End With
- With wb.Sheets("Sheet1").Range("A1:T1")
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlBottom
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- .Borders(xlDiagonalDown).LineStyle = xlNone
- .Borders(xlDiagonalUp).LineStyle = xlNone
- .Borders(xlEdgeLeft).LineStyle = xlNone
- .Borders(xlEdgeTop).LineStyle = xlNone
- End With
- With wb.Sheets("Sheet1").Range("A1:T1").Borders(xlEdgeBottom)
- .LineStyle = xlDouble
- .Color = -1389396
- .TintAndShade = 0
- .Weight = xlThick
- .Borders(xlEdgeRight).LineStyle = xlNone
- .Borders(xlInsideVertical).LineStyle = xlNone
- .Borders(xlInsideHorizontal).LineStyle = xlNone
- End With
- With wb.Sheets("Sheet1").Range("A1:T1")
- .HorizontalAlignment = xlGeneral
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- With wb.Sheets("Sheet1").Range("A1:T1")
- .HorizontalAlignment = xlCenter
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- With wb.Sheets("Sheet1").Range("A1:T1")
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlBottom
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- With wb.Sheets("Sheet1").Range("A1:T1")
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- With wb.Sheets("Sheet1").Range("C:C")
- .HorizontalAlignment = xlLeft
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- With wb.Sheets("Sheet1").Range("C1")
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- wb.Sheets("Sheet1").Range("B1").value = "Ticket ID"
- wb.Sheets("Sheet1").Range("C1").value = "Title"
- wb.Sheets("Sheet1").Range("D1").value = "Ticket step"
- wb.Sheets("Sheet1").Range("E1").value = "Priority"
- wb.Sheets("Sheet1").Range("G1").value = "Note"
- wb.Sheets("Sheet1").Range("H1").value = "Reported on"
- wb.Sheets("Sheet1").Range("I1").value = "Fix Planned at"
- wb.Sheets("Sheet1").Range("J1").value = "Mantis ID"
- wb.Sheets("Sheet1").Columns("G:G").ColumnWidth = 40.71
- wb.Sheets("Sheet1").Columns("J:J").ColumnWidth = 10.71
- wb.Sheets("Sheet1").Range("F:F").EntireColumn.Hidden = True
- wb.Sheets("Sheet1").Range("N:Q").EntireColumn.Hidden = True
- wb.Sheets("Sheet1").Range("K2")
- Do Until IsEmpty(wb.Sheets("Sheet1").Range("K2"))
- If wb.Sheets("Sheet1").Range("K2").Value = "closed" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(232, 232, 232)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "rejected" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(163, 132, 114)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "released" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(187, 187, 187)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "tested" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(170, 255, 170)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "devtested" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(188, 221, 74)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "integrated" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(44, 212, 127)
- End If
- If wb.Sheets("Sheet1").Range("K2".Value) = "resolved" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(204, 238, 221)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "assigned" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(200, 200, 255)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "confirmed" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 255, 176)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "assignedtorepro" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(238, 152, 4)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "acknowledged" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 216, 80)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "rejectcandidate" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(246, 64, 81)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "feedback" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 80, 168)
- End If
- If wb.Sheets("Sheet1").Range("K2").Value = "new" Then
- wb.Sheets("Sheet1").Range("K2").Interior.Color = RGB(255, 160, 160)
- End If
- Loop
- wb.Sheets("Sheet1").Range("A1:T30").RowHeight=15
- ' Formatting notes
- With wb.Sheets("Sheet1").Range("G2:G9")
- .HorizontalAlignment = xlLeft
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- wb.Save
- x1.Quit
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement