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
|
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call Workbook_SheetSelectionChange(Sh, Selection)
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
' Attention, faire appel a Dependents fait une boucle avec SelectionChange(Target=Dependent)
Dim td As Range, addr, colind, colind2, Target2 As Range, cel As Range
Set td = Target.Dependents
addr = Target.Address(True, True, xlA1, True)
If Err.Number = 0 Then
Do While addr = Selection.Address(True, True, xlA1, True)
' Couleur de la 1ere cellule
colind = Target.Cells(1).Interior.ColorIndex
colind2 = colind
Set Target2 = Target
If Target.Cells.Count > 1 Then
' Recherche une autre cellule dans la plage de couleur differente
For Each cel In Target.Cells
If colind <> cel.Interior.ColorIndex Then
colind2 = cel.Interior.ColorIndex
Set Target2 = cel
Exit For
End If
Next cel
End If
Do While colind = Target.Cells(1).Interior.ColorIndex And _
colind2 = Target2.Cells(1).Interior.ColorIndex And _
addr = Selection.Address(True, True, xlA1, True)
DoEvents
Loop
Set Target2 = Nothing
'Application.CalculateFull : serait un peut trop lourd s'il y a bcp de formules
For Each cel In td.Cells
' cel.Calculate ne fonctionne pas car cela revient a faire
' le [F9] manuellement qui ne fonctionne pas
' mais Application.CalculateFull fonctionnerait
cel.FormulaLocal = cel.FormulaLocal
Next cel
Set cel = Nothing
DoEvents
Loop
Else
Err.Clear
End If
Set td = Nothing
End Sub |
Partager