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 worksheet such that processing of data could run at a more reasonable faster rate & where at completion of the processing sequence the Conditional Formatting was reapplied?
I would find this most helpful (& am sure many others would also) - I ask this as I regularly analyse data of ~ 100k rows & >50 cols - even with an 8 core running at 3G2 - processing often takes >>20 mins per sheet... & with >15 separate sheets the delay becomes somewhat exasperating?
I suspect that there is a base reason why such a function cannot exist; - or surely some enterprising software writer would have observed the existence of such a requirement... I'd be most interested to attempt to understand why such may not be achieved, thank you.

excel
Formulas and Functions

4 Replies

Sort By
  • John Bebb's avatar
    John Bebb
    Copper Contributor
    Nov 28, 2018
    Hi Jan - thank you for your response. I may endeavour to apply your macros but please; - which data handlikg package may you suggest; - as I am aware that Excel at times struggles to handle large data?
    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Nov 29, 2018
      I wouldn't know software that might handle this better, you just need a different take on things I expect.
      I strongly suspect you won't be looking at the entire table all the time, but rather at subsets of the data. I would leave the large table alone and focus on making it easy to filter the table on another location in the workbook and applying conditional formatting to the filtered dataset.
  • 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