Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E2")) Is Nothing Then
        Application.EnableEvents = False
Dim i As Long
Dim iLR As Long
Dim FoundObject As Range
   iLR = Cells(Rows.Count, "A").End(xlUp).Row
   With Worksheets("Кол-во часов")
      For i = 7 To iLR
        Set FoundObject = .Columns(2).Find(Cells(i, "A"), , xlValues, xlWhole)
        If Not FoundObject Is Nothing Then
          Cells(i, "F") = FoundObject.Offset(, 2)
        End If
      Next
   End With
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E2")) Is Nothing Then
        Application.EnableEvents = False
Dim i As Long
Dim iLR As Long
Dim iLR_осв As Long
Dim FoundMonth As Range
     iLR = Cells(Rows.Count, "A").End(xlUp).Row + 1
   If iLR < 7 Then iLR = 7
   Range("A7:A" & iLR).ClearContents   'очищаем столбец А
   With Worksheets("Освещение")
   ' ищем столбец с месяцем
     Set FoundMonth = .Rows(4).Find(Target, , xlValues, xlWhole)
     If Not FoundMonth Is Nothing Then
           iLR_осв = .Cells(.Rows.Count, "B").End(xlUp).Row
           iLR = 7
        For i = 8 To iLR_осв  'цикл по столбцу с найденным месяцем
          If InStr(1, .Cells(i, FoundMonth.Column), "ТР") <> 0 Then
               Cells(iLR, "A") = .Cells(i, "B") 'оборудование, где есть ТР
               iLR = Cells(Rows.Count, "A").End(xlUp).Row + 1
          End If
        Next
     End If
   End With
    End If
    Application.EnableEvents = True
End Sub