470,632 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Excel - how to make conditional formatting permanent?

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.
Feb 25 '20 #1
4 13840
DaveBonallack
2 2Bits
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.
Mar 31 '21 #2
Killer42
8,435 Expert 8TB
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.
May 20 '21 #3
SioSio
269 256MB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim start_row As Long
  3.     Dim end_row As Long
  4.     Dim start_column As Long
  5.     Dim end_column As Long
  6.     Dim i As Long
  7.  
  8.     start_row = Target(1).Row
  9.     end_row = Target(Target.Count).Row
  10.  
  11.     start_column = Target(1).Column
  12.     end_column = Target(Target.Count).Column
  13.  
  14.     For i = start_column To end_column
  15.  
  16.         Select Case i
  17.             Case 3
  18.                 'Range C
  19.                 Range(Cells(start_row, i), Cells(end_row, i)).Font.Bold = True
  20.             Case 4
  21.                 'Range D
  22.                 Range(Cells(start_row, i), Cells(end_row, i)).Font.ColorIndex = 3    'Font color Red
  23.             Case 5
  24.                 'Range E
  25.                 Range(Cells(start_row, i), Cells(end_row, i)).Interior.ColorIndex = 27   'Background color Yellow
  26.        End Select
  27.     Next i
  28.  
  29. End Sub
May 21 '21 #4
Mia White
11 Byte
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.
Mar 31 '22 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by jeffgeorge | last post: by
4 posts views Thread by Bradley | last post: by
8 posts views Thread by Dimitri Furman | last post: by
2 posts views Thread by Von Bailey | last post: by
1 post views Thread by GGerard | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.