Excel - how to make conditional formatting permanent?

[FredNurk]

Hi all.

I've been doing a bit of searching, but I'm not sure how to even word the query.

I have a pretty big Excel workbook to which I add a few more rows each day. Each row includes a little data, and a bunch of information that Excel derives from it including various statistical info which is included in numerous charts. Due to the amount of work involved, it was getting to the point where simply inserting a row took maybe 20-30 seconds – and that's before recalculating.

The data never changes; it's always new entries being inserted at/near the bottom. So to speed things up, I grabbed tens of thousands of old rows, copied them, and did Paste-Special|Values. Voila - huge speed increase, as those rows are now entirely static values requiring no recalculation or cross-checking.

The whole workbook also uses a bunch of conditional formatting rules to highlight rows/cells for various reasons. So, to speed things up further, I'm looking for a way to do the same sort of thing with the conditional formatting. That is, to apply things like foreground/background colours permanently to the old rows, rather than their being set dynamically by testing various conditions.

Does anyone know of a convenient way to do this? If I copy, and Paste-Special|Formatting it just copies the conditional formatting. I want to instead copy the final result of the conditional formatting.

[DaveBonallack]

Hi Fred.
This can be done with a few lines of VBA, which need only be run once for each range you wanted to make static.
Code that works through a predetermined range, cell by cell
Code that removes conditional formatting from the cell
Code that applies formatting in a 1-off way using the rules you set up in the original conditional formats
Next cell in range.
Are you familiar enough with VBA to do this?
Regards - Dave.

[Killer42]

Thanks Dave, I'll give it a try. (FredNurk was me on a temporary account).

I had been hoping to find some feature that would do it for me, but with no other responses in well over a year, I suppose there's no point holding my breath.

Will post here how it goes.

[SioSio]

When you paste into a range (cells), the Worksheet_Change event is fired.
Since the pasted range is passed as a argument, set the font modification, color, and background color for each column in the pasted range.

Add the vba in the sheet to be used.

In the example below, the font for range C is Bold, the font for range D is red, and the background color for range E is yellow.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim start_row As Long
    Dim end_row As Long
    Dim start_column As Long
    Dim end_column As Long
    Dim i As Long
 
    start_row = Target(1).Row
    end_row = Target(Target.Count).Row
 
    start_column = Target(1).Column
    end_column = Target(Target.Count).Column
 
    For i = start_column To end_column
 
        Select Case i
            Case 3
                'Range C
                Range(Cells(start_row, i), Cells(end_row, i)).Font.Bold = True
            Case 4
                'Range D
                Range(Cells(start_row, i), Cells(end_row, i)).Font.ColorIndex = 3    'Font color Red
            Case 5
                'Range E
                Range(Cells(start_row, i), Cells(end_row, i)).Interior.ColorIndex = 27   'Background color Yellow
       End Select
    Next i
 
End Sub

[Mia White]

hey, try doing this.

Load the workbook that contains your conditional formatting.
Save the workbook as an HTML file. (Press F12, specify the HTML format, and give the workbook a different name.)
Restart Excel.
Load into Excel the HTML file you saved in step 2.
Save the workbook as an Excel workbook.