I have a range of data that is pulled via a query, and then I use a data range comparison to highlight data. For instance, if I pull my data in sheet 'A', and input my own data in sheet 'B', I am checking to see what data on sheet 'A' is also on sheet 'B'. This is actually accomplished via column comparison in sheet 'B', but that is not pertinent. In order for this comparison to work, I have to select the column on sheet 'A', and use the Text to Columns tool to make the data delimited in order to compare. The crux of the issue is that when this happens, or when I refresh my query, the rule and range of my conditional formatting both change, and a new rule is created.
I've tried setting the range affected to a static value, like =$A$1:$L$500,
but upon refreshing my query, the range changes to $A$1:$L$88. This is an issue because the range I need to view is actually $A$1:$L$89, and the adjusted range is always one cell shorter than needed. Additionally, a new formatting rule is created. In my original rule, the condition is ='B'!$B2=TRUE. In this new rule, that I did not create, the condition is ='B'!$B110=TRUE, and the range affected is = $A$89:$L$500.
Unfortunately, this is not where this problem ends. I mentioned earlier that in order to successfully compare the data I need, I have to use the Text to Columns tool on sheet 'A'. This does two things: it creates a new rule with condition ='B'!$B110=TRUE over range =$A$89:$L$129, and it edits the rule created earlier, changing the condition to ='B'$B111=TRUE and the range to =$A$130:$L$540.
I am really not sure how to approach this issue, and I will provide more details if needed. In summary, Rule A exists on Sheet A, and when Sheet A is refreshed, range of Rule A changes and Rule B is created. Then, when Text to Columns is used, Rule B is changed in both condition and range, and Rule C is created.