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
|
Option Explicit
Option Compare Text
'Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
'-------------------------------------------------------------------------------------------------
'DEFINTION DES VARIABLES GÉNÉRALES
Dim prem_lig As Integer, expi_lig As Integer, der_lig As Integer, der_col As Integer
prem_lig = 4
expi_lig = 2
' der_lig = 32
der_lig = Range("A" & Rows.Count).End(xlUp).Row
der_col = Cells(1, Columns.Count).End(xlToLeft).Column
'-------------------------------------------------------------------------------------------------
'DÉFINITION DES VALEURS SPÉCIFIQUES
Dim col_e As Range, col_f As Range, col_g As Range, col_h As Range, col_j As Range, col_k As Range
Set col_e = Range(Cells(prem_lig, 5), Cells(der_lig, 5))
Dim exp_f As Integer, exp_h As Integer, exp_k As Integer
exp_f = Cells(expi_lig, 6)
'-------------------------------------------------------------------------------------------------
'ColonneF()
For Each col_f In col_e
col_f.Offset(0, 1).ClearContents
If IsDate(col_f.Value) Then
col_f.Offset(0, 1).Formula = DateAdd("m", exp_f, col_f)
If col_f.Offset(0, 1).Value < Date Then
col_f.Offset(0, 1).Font.Color = RGB(192, 0, 0)
Else
End If
Else
If IsEmpty(col_f.Value) Then
col_f.Interior.Color = RGB(255, 255, 255)
Else
col_f.Offset(0, 1).Value = col_f.Offset(0, 0).Value
End If
End If
Next col_f
'-------------------------------------------------------------------------------------------------
Debug.Print der_lig
Debug.Print "mise à jour effectuée"
'-------------------------------------------------------------------------------------------------
End Sub |
Partager