Sub Çàäàíèå1 Îáúÿâëÿåì ëèñòû Set Trips Worksheets Êîìàíäèðîâêè Set Goa

Sub Çàäàíèå1()

    ' Îáúÿâëÿåì ëèñòû
    Set Trips = Worksheets("Êîìàíäèðîâêè")
    Set Goals = Worksheets("Ñïèñîê öåëåé")
    
    ' Îáúÿâëÿåì àññîöèàòèâíûé ìàññèâ
    Dim Goal_Array As New Dictionary
    
    ' Îáúÿâëÿåì äëèíû ëèñòîâ
    Dim Goal_Row, Trip_Row As Integer
    
    ' Ïîëó÷àåì äëèíû ëèñòîâ
    Goal_Row = Goals.Cells(2, 1).CurrentRegion.Rows.count
    Trip_Row = Trips.Cells(2, 1).CurrentRegion.Rows.count
    
    ' Çàïîëíÿåì àññîöèàòèâíûé ìàññèâè
    For i = 2 To Goal_Row
        
        Goal_Array.Add Goals.Cells(i, 1).Value, Goals.Cells(i, 2).Value
    
    Next i
    
    ' Ïåðåáèðàåì âñå ñðîêè êîìàíäèðîâêè
    For i = 2 To Trip_Row
    
        ' Ïðîâåðêà öåëè êîìàíäèðîâêè
        If Goal_Array.Item(Trips.Cells(i, 4).Value) Then
        
            ' Ïðîâåðêà ìàêñèìàëüíîãî ñðîêà êîìàíäèðîâêè
            If (CInt(Goal_Array.Item(Trips.Cells(i, 4).Value)) < CInt(Trips.Cells(i, 3))) Then
                 
                 ' Îêðàøèâàåì îøèáêè
                 Trips.Cells(i, 3).Interior.Color = RGB(255, 219, 190)
                 
                 ' Èçìåíÿåì ñðîêè íà äîïóùåííûå
                 Trips.Cells(i, 3) = Goal_Array.Item(Trips.Cells(i, 4).Value)
            
            End If
        
        End If
    
    Next
    
End Sub