472,952 Members | 2,378 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

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 17168
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
272 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
10 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

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

Similar topics

3
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be...
2
by: jeffgeorge | last post by:
Trying to add 3 colors to 6 combinations of text possibilities. If this makes sense, the possibilities are: A D (one color for the combo beginning with A) A E B D (One color for the combo...
4
by: Bradley | last post by:
I have an A2000 database in which I have a continuous form with a tick box. There is also a text box with a conditional format that is based on the expression , if it's true then change the...
8
by: Dimitri Furman | last post by:
Given: Access 2002/2003 A subform in datasheet or continuous view, placed on a tab page (this last may or may not matter) Conditional formatting applied to some controls on the subform - format...
2
by: Von Bailey | last post by:
I have a form where the conditional formatting is set on some fields to bold if certain conditions are met. However, when the conditions are met some of the data that is to bold is either not...
1
by: GGerard | last post by:
Hello Is there a way to use a variable in the Conditional Formatting of a Textbox? Example : I want the background of a textbox in a continuous form to change color when the value of...
8
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
10
by: afromanam | last post by:
Regards, Please help What I'm trying to do is this: (and I can't use reports since I must export to Excel) I export some queries to different tabs in an excel workbook I then loop through...
13
by: liztowne | last post by:
I'm putting together a report (a very simple one) where I'm grouping by crime type (all, violent, property), displaying city and district data with percent change. I need to format the field...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.