Conditional Formatting Ranges Increasing on Data Refresh

[dappolan]

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 clean and easy to read, including a rule for row 1, which I've frozen as a header. The issue I'm having is that even though my macro is pasting only values into the output sheet, when I load updated data into the workbook, the range for my conditional formatting increases. Even though the range is expressed as $1:$1, after refresh, the range is $1:$5. Successive refreshes result in $1:$17, then 42, 90, and 208. The other rules are being messed with as well. I don't see a mathematical relationship between these numbers, and I have no clue why the formatting is being affected like this. 

Please advise!

[#2]

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?

[#3]

@dappolan 

Here are a few possible reasons why your conditional formatting ranges are increasing on data refresh.

• The data you are pasting is not being pasted as values. If the data you are pasting is formulas or text, then Excel will treat the entire range of cells as dynamic, and the conditional formatting ranges will be affected accordingly. To fix this, make sure that the data you are pasting is only values.
• The conditional formatting rules are using relative references. If the conditional formatting rules are using relative references, then the ranges will be adjusted when the data is refreshed. To fix this, make sure that the conditional formatting rules are using absolute references.
• There is a bug in Excel. It is possible that there is a bug in Excel that is causing the conditional formatting ranges to increase. If you have tried the above solutions and the problem persists, then you can try updating Excel to the latest version.

To troubleshoot the issue, you can try the following steps:

1. Check the data you are pasting to make sure that it is being pasted as values.
2. Check the conditional formatting rules to make sure that they are using absolute references.
3. Update Excel to the latest version.

Here are some additional tips to help you prevent conditional formatting from changing:

• Use absolute references in your conditional formatting rules.
• Protect your worksheets so that users cannot accidentally change the conditional formatting rules.
• Use a macro to clear the conditional formatting rules before you refresh your data.

The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#4]

I'll check, and should be able to share the code as it's not even a little proprietary, but there shouldn't be any reason for it to be inserting columns. The range it's being copied to is cleared before pasting, and while the code subsequently calls macros that do involve deleting/inserting rows, I don't feel that this is the issue, as there's no discernible mathematical relationship between the range sizes at each step. I'll try to share some later.

[#5]

@OkenAnalytics @NikolinoDE 

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.