1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
| Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Count = 1 And Target.Row > 2 And Target.Column = 1 Then
With Range("b6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R3C1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
With Range("b6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=""à"""
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
With Range("b6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R4C1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
With Range("c6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End With
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((VALUE(R6C2:R[-1]C2)>=R3C1)*(VALUE(R6C2:R[-1]C2)<=R4C1)*(R6C3:R[-1]C3))"
Selection.Style = "Comma"
Selection.NumberFormat = _
"_-* #,##0.000 __-;-* #,##0.000 __-;_-* ""-""?? __-;_-@_-"
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
Selection.NumberFormat = _
"_-* #,##0.0 __-;-* #,##0.0 __-;_-* ""-""?? __-;_-@_-"
Selection.NumberFormat = "_-* #,##0 __-;-* #,##0 __-;_-* ""-""?? __-;_-@_-"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub |
Partager