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