Forum Discussion
Conditional Formatting Formula Changes Erratically
I am trying to format a cell if it is not equal to another cell, but the formula I type in changes itself.
Here is what I did, starting with a blank sheet.
typed x into A1
typed y into A2
clicked >Conditional Formatting button, >New Rule, >Use a formula to determine which cells to format
the rule description is =if(a1=a2,0,1)
clicked >Format button, set color to red, clicked>OK
clicked OK in New Formatting Rule window
(can't specify where to apply, so I click >Conditional Formatting, >Manage Rules)
change >Applies to to =A1:A5
Click apply
The >Applies to changes to =$A$1:$A$5
**The formula changes to =IF(A1048575=A1048576,0,1)**
Has anyone encountered this before?
The Conditional Formatting is having other issues as well, for example the cells are formatted the same but behave differently. I will try to document later.
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.
10 Replies
Yes, that's normal behaviour if you create the rule not from top left cell of the range. Most probably you was on some other cell in column A, if repeat the same from another column that will be transformed to
=IF(XFC1048575=XFC1048576,0,1)- your steps seems perfect.
can u share the file, i can check furtherbhushan_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.
