' Region Description
'
' Name:
' Author:
' Version:
' Description:
'
'
' EndRegion

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\Batcholas\as.xlsx")
Sub DailyWarRoom()

' 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"

    wb.Sheets(1).Range("A1:T1").Select
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 13
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    With Selection.Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Color = -1389396
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    wb.Sheets(1).Range("B1").Select
    ActiveCell.FormulaR1C1 = "Ticket ID"
    wb.Sheets(1).Range("C1").Select
    ActiveCell.FormulaR1C1 = "Title"
    wb.Sheets(1).Range("D1").Select
    ActiveCell.FormulaR1C1 = "Ticket step"
    wb.Sheets(1).Range("E1").Select
    ActiveCell.FormulaR1C1 = "Priority"
    wb.Sheets(1).Range("G1").Select
    ActiveCell.FormulaR1C1 = "Note"
    wb.Sheets(1).Range("H1").Select
    ActiveCell.FormulaR1C1 = "Reported on"
    wb.Sheets(1).Range("I1").Select
    ActiveCell.FormulaR1C1 = "Fix planned at"
    wb.Sheets(1).Range("J1").Select
    ActiveCell.FormulaR1C1 = "Mantis ID"
    Cells.Select
    Cells.EntireColumn.AutoFit
    wb.Sheets(1).Range("H3").Select
    Columns("G:G").ColumnWidth = 40.71
    Columns("J:J").ColumnWidth = 10.71
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Columns("N:N").Select
    Columns("N:Q").Select
    Selection.EntireColumn.Hidden = True

    wb.Sheets(1).Range("K2").Select

      Do Until IsEmpty(ActiveCell)
         If ActiveCell.Value = "closed" Then
    ActiveCell.Interior.Color = RGB(232, 232, 232)
    End If

    If ActiveCell.Value = "rejected" Then
    ActiveCell.Interior.Color = RGB(163, 132, 114)
    End If

    If ActiveCell.Value = "released" Then
    ActiveCell.Interior.Color = RGB(187, 187, 187)
    End If

    If ActiveCell.Value = "tested" Then
    ActiveCell.Interior.Color = RGB(170, 255, 170)
    End If

    If ActiveCell.Value = "devtested" Then
    ActiveCell.Interior.Color = RGB(188, 221, 74)
    End If

    If ActiveCell.Value = "integrated" Then
    ActiveCell.Interior.Color = RGB(44, 212, 127)
    End If

    If ActiveCell.Value = "resolved" Then
    ActiveCell.Interior.Color = RGB(204, 238, 221)
    End If

    If ActiveCell.Value = "assigned" Then
    ActiveCell.Interior.Color = RGB(200, 200, 255)
    End If

    If ActiveCell.Value = "confirmed" Then
    ActiveCell.Interior.Color = RGB(255, 255, 176)
    End If

    If ActiveCell.Value = "assignedtorepro" Then
    ActiveCell.Interior.Color = RGB(238, 152, 4)
    End If

    If ActiveCell.Value = "acknowledged" Then
    ActiveCell.Interior.Color = RGB(255, 216, 80)
    End If

    If ActiveCell.Value = "rejectcandidate" Then
    ActiveCell.Interior.Color = RGB(246, 64, 81)
    End If

    If ActiveCell.Value = "feedback" Then
    ActiveCell.Interior.Color = RGB(255, 80, 168)
    End If

    If ActiveCell.Value = "new" Then
    ActiveCell.Interior.Color = RGB(255, 160, 160)
    End If
    ActiveCell.Offset(1, 0).Select
Loop
wb.Sheets(1).Range("A1:T30").Select
Selection.RowHeight = 15

    ' Noteok balra rendezes
    Range("G2:G9").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub

wb.Save
'x1.Quit