Sub CreateCategory(letter As String, number As Integer)
    Dim l2 As String: l2 = Chr(Asc(letter) + 1)
    Dim n2 As Integer: n2 = number 'vyhozeno # a název
    
    Range(letter & number & ":" & l2 & number).Select
    Selection.Merge
    
    Range(letter & number & ":" & l2 & (n2 + 8)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.HorizontalAlignment = xlLeft
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    Range(letter & number & ":" & l2 & number).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    '1 -> 8
    For i = 1 To 8
        Range(letter & (n2 + i)).Value = i
        'color
        If i Mod 2 = 1 Then
            Range(letter & (n2 + i) & ":" & l2 & (n2 + i)).Interior.Color = nazev1
        Else
            Range(letter & (n2 + i) & ":" & l2 & (n2 + i)).Interior.Color = nazev2
        End If
    Next
        
    'Upper line
    Range(letter & number & ":" & l2 & number).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .Interior.Color = RGB(210, 210, 210)
    End With
    
End Sub

Sub FormatName(r As String, text As String)
    Range(r).Select
    ActiveCell.FormulaR1C1 = text
    With ActiveCell.Characters(start:=1, Length:=0).Font
        .Name = "Calibri"
        .FontStyle = "Obyčejné"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(start:=1, Length:=1).Font
        .Name = "Calibri"
        .FontStyle = "Tučné"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(start:=2, Length:=3).Font
        .Name = "Calibri"
        .FontStyle = "Obyčejné"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(start:=5, Length:=50).Font
        .Name = "Calibri"
        .FontStyle = "Kurzíva"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    
End Sub

Public editing As Boolean
Public id1, id2, nazev1, nazev2, bank1, bank2, poznamka1, poznamka2
Public program, setup
Public doprovod, riff, song
       
       
Public Sub generateProgramsList()
    initColors
    Dim clist
    clist = Array("Piano 1", "Piano 2", "E Piano 1", "E Piano 2", "PopKeys", "Clavier", "Organ", "Brass")
    
    List7.Select
    
    'clear
    Rows("3:1000").Clear
    Rows("3:1000").EntireRow.AutoFit
    Range("A3:Z1000").Select
    Selection.VerticalAlignment = xlTop
    Selection.Font.Size = 12
    
    'load all names from programs list
    Dim names(1 To 1000) As String
    List1.Select
    For i = 2 To 1000
        names(i - 1) = Range("E" & i).Value
    Next
    
    'draw data
    List7.Select
    Dim l As String: l = "A"
    Dim n As Integer: n = 4
    Dim l2 As String
    Dim catn As Integer: catn = 1
    
    i = 1
    Dim nz As String
    While True
        l2 = Chr(Asc(l) + 1)
        
        nz = names(i)
        If nz = "" Then GoTo p
        
        CreateCategory l, n
        
        'cat
        For o = 1 To 8
            Range(l2 & n + o).Value = names(i)
            FormatName l & n & ":" & l2 & n, catn & " - " & clist(catn - 1)
                    
            i = i + 1
        Next
        
        'move to next position
        catn = catn + 1
        
        l = Chr(Asc(l) + 3)
        If (catn - 1) Mod 8 = 0 Then
            n = n + 10
            l = "A"
        End If
    Wend
    
p:
    'END
    
    Columns("A:Z").ColumnWidth = 8.5
    Columns("A:Z").EntireColumn.AutoFit
    Range("A1").Select
            
End Sub

       
Public Sub initColors()
    id1 = RGB(255, 255, 200)
    id2 = RGB(255, 255, 100)
    nazev1 = RGB(208, 248, 153)
    nazev2 = RGB(152, 243, 73)
    bank1 = RGB(188, 238, 254)
    bank2 = RGB(140, 225, 253)
    poznamka1 = RGB(253, 221, 185)
    poznamka2 = RGB(251, 197, 137)
    
    program = RGB(208, 153, 253)
    setup = RGB(254, 152, 154)
    
    doprovod = program: riff = setup
    song = RGB(100, 220, 250)
End Sub

Public Sub formatSongs()
    Dim i As Integer: i = 2
    
    While i < 100
        If Range("A" & i).Value = "" Then GoTo over
                
        'colorize
        If i Mod 2 = 0 Then
            Range("A" & i).Interior.Color = id1
            Range("C" & i).Interior.Color = nazev1
            For q = 1 To 19
                Range(Chr((Asc("D") + q - 1)) & i).Interior.Color = poznamka1
            Next
        Else
            Range("A" & i).Interior.Color = id2
            Range("C" & i).Interior.Color = nazev2
            For q = 1 To 19
                Range(Chr((Asc("D") + q - 1)) & i).Interior.Color = poznamka2
            Next
        End If
        
        If Range("B" & i).Value = "doprovod" Then
            Range("B" & i).Interior.Color = doprovod
        ElseIf Range("B" & i).Value = "riff" Then
            Range("B" & i).Interior.Color = riff
        Else
            Range("B" & i).Interior.Color = song
        End If
        
over:
        i = i + 1
    Wend
End Sub

Public Sub formatPrograms()
    Dim i As Integer: i = 2
    
    While i < 100
        If Range("A" & i).Value = "" Then GoTo over
                
        'colorize
        If i Mod 2 = 0 Then
            Range("A" & i).Interior.Color = id1
            Range("B" & i).Interior.Color = id1
            Range("C" & i).Interior.Color = id1
            Range("E" & i).Interior.Color = nazev1
            Range("F" & i).Interior.Color = bank1
            Range("G" & i).Interior.Color = bank1
            For q = 1 To 19
                Range(Chr((Asc("H") + q - 1)) & i).Interior.Color = poznamka1
            Next
        Else
            Range("A" & i).Interior.Color = id2
            Range("B" & i).Interior.Color = id2
            Range("C" & i).Interior.Color = id2
            Range("E" & i).Interior.Color = nazev2
            Range("F" & i).Interior.Color = bank2
            Range("G" & i).Interior.Color = bank2
            For q = 1 To 19
                Range(Chr((Asc("H") + q - 1)) & i).Interior.Color = poznamka2
            Next
        End If
        
        If Range("D" & i).Value = "p" Then
            Range("D" & i).Interior.Color = program
        Else
            Range("D" & i).Interior.Color = setup
        End If
        
over:
        i = i + 1
    Wend
    
End Sub

Public Sub formatDurman()
    Dim i As Integer: i = 2
    Dim o As Integer: o = 2
    
    While i < 1000
        If Range("A" & i).Value = "" Then GoTo over
                
        'colorize
        If o Mod 2 = 0 Then
            Range("A" & i).Interior.Color = id1
            Range("B" & i).Interior.Color = id1
            Range("C" & i).Interior.Color = id1
            Range("E" & i).Interior.Color = nazev1
            Range("F" & i).Interior.Color = bank1
            Range("G" & i).Interior.Color = bank1
            Range("H" & i).Interior.Color = poznamka1
            Range("I" & i).Interior.Color = poznamka1
        Else
            Range("A" & i).Interior.Color = id2
            Range("B" & i).Interior.Color = id2
            Range("C" & i).Interior.Color = id2
            Range("E" & i).Interior.Color = nazev2
            Range("F" & i).Interior.Color = bank2
            Range("G" & i).Interior.Color = bank2
            Range("H" & i).Interior.Color = poznamka2
            Range("I" & i).Interior.Color = poznamka2
        End If
        
        If Range("D" & i).Value = "p" Then
            Range("D" & i).Interior.Color = program
        Else
            Range("D" & i).Interior.Color = setup
        End If
        
        o = o + 1
over:
        i = i + 1
    Wend
    
End Sub

Public Sub generatePlayList()
    initColors

    Dim radkovani As Integer: radkovani = 1

    'neefektivně naprogramované, ale pro pár položek bohatě stačí...
    Dim nazev As String
    Dim nalezeno As Boolean
    Dim id As String
    Dim q1 As String, q2 As String 'quick access
    Dim nzv As String, typ As String
    Dim ovladani As String, poznamka As String
                
    Dim radek As Integer: radek = 43
    Dim rdk As Integer: rdk = 0
    
    List6.Select
    
    'smažu co je nyní, nastavím zpět velikosti
    Rows("43:1000").Clear
    Rows("43:1000").EntireRow.AutoFit
    
    Range("A42:G1000").Select
    Selection.VerticalAlignment = xlTop
    Selection.Borders.LineStyle = xlContinuous
        
    Range("D2:D39").Value = ""
    Range("D2:D39").Interior.Color = Range("G1").Interior.Color
    
    'hledám
    For i = 2 To 39
        'co hledam
        nazev = UCase(Trim(Range("A" & i).Value))
        If nazev = "" Then Exit For 'uz jsme vse prohledali
        
        'hledam na listu DURMAN
        List5.Select
        nalezeno = False
        For o = 1 To 1000
            If UCase(Range("E" & o).Value) = nazev Then
                
                'načtení dat
                id = Range("A" & o).Value
                typ = Range("D" & o).Value
                nzv = Range("E" & o).Value
                q1 = Range("F" & o).Value
                q2 = Range("G" & o).Value
                ovladani = Range("H" & o).Value
                poznamka = Range("I" & o).Value
                
                nalezeno = True
                Exit For
            End If
        Next
        
        'vyhodnoceni
        If nalezeno Then
            List6.Select
            Range("D" & i).Value = "ANO"
            Range("D" & i).Interior.Color = vbGreen
            
            'vypsání dat
            Range("A" & radek).Value = id
                If rdk Mod 2 = 0 Then Range("A" & radek).Interior.Color = id1 Else Range("A" & radek).Interior.Color = id2
            Range("B" & radek).Value = typ
                If typ = "p" Then Range("B" & radek).Interior.Color = program Else Range("B" & radek).Interior.Color = setup
            Range("C" & radek).Value = nzv
                If rdk Mod 2 = 0 Then Range("C" & radek).Interior.Color = nazev1 Else Range("C" & radek).Interior.Color = nazev2
            Range("D" & radek).Value = q1
                If rdk Mod 2 = 0 Then Range("D" & radek).Interior.Color = bank1 Else Range("D" & radek).Interior.Color = bank2
            Range("E" & radek).Value = q2
                If rdk Mod 2 = 0 Then Range("E" & radek).Interior.Color = bank1 Else Range("E" & radek).Interior.Color = bank2
            Range("F" & radek).Value = ovladani
                If rdk Mod 2 = 0 Then Range("F" & radek).Interior.Color = poznamka1 Else Range("F" & radek).Interior.Color = poznamka2
            Range("G" & radek).Value = poznamka
                If rdk Mod 2 = 0 Then Range("G" & radek).Interior.Color = poznamka1 Else Range("G" & radek).Interior.Color = poznamka2
                
            radek = radek + radkovani
            rdk = rdk + 1
        Else
            'nenalezeno
            List6.Select
            Range("D" & i).Value = "NE"
            Range("D" & i).Interior.Color = vbRed
        End If
                
    Next
    
    'vybrání výsledku
    Range("A42:G" & (radek - radkovani)).Select
        
End Sub