Forum Discussion
Conditional Formatting Ranges Increasing on Data Refresh
Hi all, I have a spreadsheet that I've created to clean and sort data to create a planner of sorts for my team. I have a lot of conditional formatting rules in the output sheet to keep things cle...
Would you like to check the paste action, perhaps the system interprets it as an insert column action? I am no VBA expert but the only explanation for this 'anomaly' is that the paste as values action inserts a column first, which would inevitably expand the conditional formatting range.
Can you share the code in part or whole?
Can you share the code in part or whole?
After reviewing my code and formatting rules, I've been unable to figure out a cause for this behavior. The formatting rules are written with absolute references, and while I do have code that inserts rows into the document, it does so to separate out events that occur on different dates, so when refreshing the data without changed entries, I would expect to see a predictable increase in the ranges, but this is not the case and the range increases far faster than the number of rows inserted. Additionally, if the code was interpreting the paste action as inserting rows, I would expect the initial increases in the format range to be much greater than they are. It's not the most elegant solution, but I've made a hidden sheet in the workbook to store my desired formatting, and my code now copies and pastes the formatting from that sheet to the operating sheet after performing the needed operations.