Forum Discussion
Conditional Formatting Formula Changes Erratically
Forgot to say, it's when changing the range we move reference in the formula out of the sheet. If modify my pervious example, start the rule from cell A17 with formula =A11>2 and after that apply the range to =A11:A15 when reference in the formula will be changed on =A5>2, i.e. on 6 rows up.
bhushan_z File is attached, but the issue is more the process than the end result, so to duplicate it you would need to follow the steps.
Forgot to say, it's when changing the range we move reference in the formula out of the sheet. If modify my pervious example, start the rule from cell A17 with formula =A11>2 and after that apply the range to =A11:A15 when reference in the formula will be changed on =A5>2, i.e. on 6 rows up.
- Thanks for the explanation.
I understand that it is reproducible, and I understand that changing the reference causes the cell in the formula to move off the sheet.
I disagree that this is "expected behavior". I would have expected the formula I entered to stay the same, or at least some sort of warning that is has been changed automatically. 🙂
I have another question about conditional formatting, but I will post it separately for clarity.
Thanks!Formula can't be the same if you change ApplyTo range. That's the nature of conditional formatting, it iterates the formula within the range offsetting relative references in it to the "first" (top left) cell of the range. If we change the range manually it applies exactly the same offsetting. Same do Format Painter, and the same approach is for named formulas.
Formula will be kept the same only in the part where absolute references are used.
If you move the reference in the formula out of the sheet area we could expect some kind of error returned. Within conditional formatting we don't see what exactly formula returns (#REF in this case), thus it's end of the sheet range here.
I am confused by your answer and when I try to duplicate your example I can't get it to work.
I have a worksheet that has A1 through A30 with the days of the month of June for example. In the rules manager for cell A1 I put the formula =A1=Today(), format that condition with a yellow fill, so each passing day the next cell has a yellow background. My issue is I want the row A1:P1 filled with yellow on June 1, B1:P1 filled yellow on June 2, etc. I am experiencing the same problem, in cell B1 in the Applies to field I enter the cell range =B1:P1 but when I hit Apply the reference changes to =$B$1:$P$1. Your video clip shows how you change the absolute reference and get it to stay but mine will not change.
Can you help with my problem?
Paul00
