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
| 'changement formule pour % difference avec année N-1 + format conditionnel + format %
Sub RemplacementFormuleongletmois()
Dim c As Range
Dim d As Range
Dim firstAddress As String
Dim dfirstAddress As String
Dim fl As Worksheet
For Each fl In Worksheets
If fl.Name <> "dashboard" And fl.Name <> "listes" Then 'attention aux noms de feuilles avec accent
'ton code
With Range("L7:L9,L12:L22,L26:L37,N7:N9,N12:N22,N26:N37,W7:W9,W12:W22,W26:W37,Y7:Y9,Y12:Y22,Y26:Y37")
Set c = .Find(What:="=INDIRECT.EXT", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.FormulaR1C1 = "=(RC[-1] - " & Mid(c.FormulaR1C1, 2) & ")/" & Mid(c.FormulaR1C1, 2)
.NumberFormat = "0.00%"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Color = -11489280
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
Set c = .FindNext(c)
Loop While Not c Is Nothing 'And c.Address <> firstAddress
End If
With Range("P7:P9,P12:P22,P26:P37,R7:R9,R12:R22,R26:R37,AA7:AA9,AA12:AA22,AA26:AA37,AC7:AC9,AC12:AC22,AC26:AC37")
Set d = .Find(What:="=INDIRECT.EXT", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not d Is Nothing Then
dfirstAddress = d.Address
Do
d.FormulaR1C1 = "=(RC[-1] - " & Mid(d.FormulaR1C1, 2) & ")"
.NumberFormat = "0.00%"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Color = -11489280
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
Set d = .FindNext(d)
Loop While Not d Is Nothing 'And dfirstAddress <> d.Address
End If
End With
End With
End If
Next fl
End Sub |
Partager