468,114 Members | 2,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,114 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
1 4344
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.
1 Week Ago #2

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
3 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.