PDA

View Full Version : Recovering conditional Formatting after running Macro



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

mikerickson
08-30-2008, 01:00 PM
You can't. Deleting cells takes them away with all their contents formats etc. They no longer exist.

But if row 12 has the formatting (inculding Conditional) it looks like your code will fill that down.