guatelize
08-29-2008, 02:10 AM
Every time I'm running my macro, my conditional formatting is deleted.
How can I keep the formatting, by writing a macro with more than 3 conditions ?
Macro :
It looks in column range F13:F1000 if data is present and will fill down column range S13:W1000 with formula from range S12:W12. If no data is present in column F it deletes the empty rows in range S13:W1000 but also deletes the conditional formatting. How can I keep the formatting and create more than 3 conditions ?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("F13:F1000"), ActiveSheet.UsedRange)
For ix = Rng.Count To 13 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
LastRow = Range("F1000").End(xlUp).Row
Range("S12:W1000").ClearContents
Range("S11:W11").AutoFill Range("S11:W" & LastRow), xlFillDefault
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End
End Sub
CF formats Totals & Subtotal with colors + font bold + border line Top + bottom :
For Range A11:W1000
=LEFT($A11;5)="Grand" : Color Blue + Font Bold + Border Line Top & Bottom
=RIGHT($A11;5)="Total" : Color Red + Font Bold + Border Line Top & Bottom
=RIGHT($B11;5)="Total" Color Yellow + Font Bold + Border Line Top & Bottom
=RIGHT($C11;5)="Total" Color Green + Font Bold + Border Line Top & Bottom
=RIGHT($D11;5)="Total" Color Orange + Font Bold + Border Line Top & Bottom
Thanks for your help
How can I keep the formatting, by writing a macro with more than 3 conditions ?
Macro :
It looks in column range F13:F1000 if data is present and will fill down column range S13:W1000 with formula from range S12:W12. If no data is present in column F it deletes the empty rows in range S13:W1000 but also deletes the conditional formatting. How can I keep the formatting and create more than 3 conditions ?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("F13:F1000"), ActiveSheet.UsedRange)
For ix = Rng.Count To 13 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
LastRow = Range("F1000").End(xlUp).Row
Range("S12:W1000").ClearContents
Range("S11:W11").AutoFill Range("S11:W" & LastRow), xlFillDefault
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End
End Sub
CF formats Totals & Subtotal with colors + font bold + border line Top + bottom :
For Range A11:W1000
=LEFT($A11;5)="Grand" : Color Blue + Font Bold + Border Line Top & Bottom
=RIGHT($A11;5)="Total" : Color Red + Font Bold + Border Line Top & Bottom
=RIGHT($B11;5)="Total" Color Yellow + Font Bold + Border Line Top & Bottom
=RIGHT($C11;5)="Total" Color Green + Font Bold + Border Line Top & Bottom
=RIGHT($D11;5)="Total" Color Orange + Font Bold + Border Line Top & Bottom
Thanks for your help