Forum Discussion

John Bebb's avatar
John Bebb
Copper Contributor
Nov 28, 2018

Macro to "park" Conditional formatting?

Hi Peoples; I'm certainly not too adept at writing macros - but please; - would it be feasible for someone to write a marco which effectively "parked" (witheld) all Conditional Formatting from a wor...
excel
Formulas and Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
Nov 28, 2018

I would argue that applying CF to such an amount of data is overstepping the limits of Excel. That being said, it would be fairly easy to copy the current CF rules from the first row of the table elsewhere and subsequently delete all CF rules from the table. Afterwards you can simply copy that parked row and paste special formats it on top of the entire table again.

Assuming the sheet only contains one table and that table starts on row 3 (header row) and row 1 is free then these two macro's should help:

Sub MoveCFAndClear()
    Intersect(Range("4:4"), ActiveSheet.UsedRange).Copy
    Intersect(Range("1:1"), ActiveSheet.UsedRange.EntireColumn).PasteSpecial xlPasteFormats
    Range("A3").CurrentRegion.Offset(1).FormatConditions.Delete
End Sub

Sub ReapplyCFrules()
    Intersect(Range("1:1"), ActiveSheet.UsedRange.EntireColumn).Copy
    Range("A3").CurrentRegion.Offset(1).PasteSpecial xlPasteFormats
End Sub

Resources