[기초방] VBA 100제 #33 [ 칸트 차트만들기 ]

Sub 기초방33()

    Dim rngAll As Range: Set rngAll = Sheets("근무일정").[b3:b10]
    Dim rngA As Range
    Dim rngX As Range: Set rngX = Sheets(2).[a3]
    Dim rngCol As Range
    Dim Vtemp
    Dim start&, bln As Boolean
    
    Haja_Format bln                                 '= 초기화
    
    Sheets("근무자추출").Activate                   '= 근무자 추출시트 활성화
    
    Application.Wait Now + TimeSerial(0, 0, 1)      '= 1초 지연
    
    
    For Each rngA In rngAll
    
        Vtemp = Split(rngA, " - ")
    
        rngX(1, 1) = rngA(1, 0)                     '= 고객명
        rngX(1, 2) = rngA(1, 2)                     '= 파견근로자1
        rngX(1, 3) = rngA(1, 3)                     '= 파견근로자2
        
        start = CLng(Split(Vtemp(0), ".")(1))       '= 시작일
        
        Set rngCol = rngX(1, 3 + start).Resize(1, CLng(Split(Vtemp(1), ".")(1)) - start + 1)
                                                    '= 종료일에서 시작일을 뺀 기간 설정

        haja_color rngCol, rngA                     '= 컬러 설정
        
        Set rngX = rngX.Offset(1)                   '= 다음 고객명 설정
        
    
    Next rngA
    
    bln = Not bln
    Haja_Format bln

End Sub

Function Haja_Format(bln As Boolean)

    Dim rngT As Range
    
    If bln = False Then Sheets(2).Cells.Interior.Color = xlNone: Exit Function

    With Sheets(2)
    
        Set rngT = .[a1].CurrentRegion
        Set rngT = rngT.Offset(2).Resize(rngT.Rows.Count - 2, 31 + 3)
        
        
    End With
    
    
    rngT.Borders.LineStyle = 1
    rngT.HorizontalAlignment = xlCenter

End Function


Function haja_color(rngT As Range, rngA As Range)

    Dim rngb As Range
    Dim i&
    
    
    For i = 1 To 50
    
    
        For Each rngb In rngT
        
            rngb.Interior.ColorIndex = WorksheetFunction.RandBetween(1, 50)
        
        Next rngb
    
    
    Next i
    
    rngT.Interior.Color = rngA.Interior.Color
    

End Function

기초방33(haja).xlsm

0.03MB