=ЕСЛИ(D5>0;ИНДЕКС('данные 1С'!$B$14:$F$250;ПОИСКПОЗ('1'!$B$1&$C5;ЗНАЧЕН('данные 1С'!$F$14:$F$250)&'данные 1С'!$B$14:$B$250;0);4)  ;)

=СУММПРОИЗВ(($B$1=ЗНАЧЕН('данные 1С'!$F$14:$F$250))*($C5='данные 1С'!$B$14:$B$250)*'данные 1С'!$E$14:$E$250)

Sub McCopy()
    Dim lsRow_1&, lsRow_2&, Rng As Range, sh As Worksheet
    lsRow_1 = Cells(Rows.Count, 1).End(xlUp).Row
    Set sh = Sheets("Лист2")
    If lsRow_1 >= 3 Then
        Range("A3:H" & lsRow_1).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        Set Rng = Intersect(Range("G3:G" & lsRow_1).SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers).EntireRow, Range("A3:H" & lsRow_1))
        lsRow_2 = sh.Cells(Rows.Count, 1).End(xlUp).Row
        If lsRow_2 >= 3 Then sh.Range("A3:H" & lsRow_2).ClearContents
        Rng.Copy Destination:=sh.Range("A3")
        Range("A3").Select
    End If
End Sub

=ЕСЛИ($E14=0;1;)

F14:K23

Range("K1").Dirty

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("F3").Dirty
End Sub

Range("F3:P3").Dirty

Union(Range("F3"), Range("H1")).Dirty

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("K1").Dirty
End Sub

Option Explicit
Private Function ColColor(Диапазон As Range)
Dim rCell As Range, Sum As Long, Sum2 As Long
    With Диапазон
        For Each rCell In .Cells
            If rCell.Interior.ColorIndex <> xlNone Then
                If Sum > 0 Then
                    Sum2 = rCell.Row
                Else
                    Sum = rCell.Row
                End If
            End If
        Next rCell
    End With
    ColColor = Sum2 - Sum + 1
End Function

# ##0,00%;[Красный]-# ##0,00%;-;

Option Explicit

Private Function ColColor(Цвет As Range, Диапазон As Range)
Dim rCell As Range, nCol As Long, Sum As Long, Sum2 As Long
    nCol = Цвет.Interior.ColorIndex
    With Диапазон
        For Each rCell In .Cells
            If rCell.Interior.ColorIndex = nCol Then
                If Sum > 0 Then
                    Sum2 = rCell.Row
                Else
                    Sum = rCell.Row
                End If
            End If
        Next rCell
    End With
    ColColor = Sum2 - Sum + 1
End Function

Option Explicit
Private Sub UserForm_Initialize()
Dim ArrTovar
Dim lRws As Long
    With Worksheets("списки"    
     lRws = .Cells(Rows.Count, "D"    .End(xlUp).Row + 1
     ArrTovar = .Range("D3    " & lRws).Value
    End With
    
    Me.lBoxTovar.List = ArrTovar
End Sub

Private Sub lBoxTovar_Click()
Dim lUnits As Long, ii&
    lUnits = InputBox(vbCrLf & Me.lBoxTovar.Value, "Вписать количество"    
    With lBoxTovCol
     .ColumnCount = 2
     .ColumnWidths = "100;20"
     ii = .ListCount
     .AddItem
     .List(ii, 0) = Me.lBoxTovar.List(Me.lBoxTovar.ListIndex, 0)
     .List(ii, 1) = lUnits
    End With
End Sub