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.
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
Thanks, I thought I tried everything. I was trying to copy the formula into the adjacent cells with the fill handle, but I see I didn't need to do that.
Paul