Forum Discussion
Conditional Formatting error
I'm not sure anyone is understanding my question. It's not an issue of what my code is. My issue is that Excel changes it, when I save it, from =$B2="Yes" to =$B1048541="Yes". when I first create it. Then, I have to go back into the code and change it back to =$B2="Yes" before it will work.
- I know this is an old post, but I have suffered with this, but have found the answer, see here:
https://stackoverflow.com/questions/62333388/excel-conditional-formatting-formula-changes-after-i-apply-the-rule
(It's all to do with which cell is active when you enter relative cell values ie cell addresses with 1 or no $ signs) We do understand your question; it's just that you didn't refer to the correct cell when you created the rule.
It is just a by-product of the rubbish idea of relative referencing that has been a defining characteristic of the electronic spreadsheet from its inception. If
= ($B2="Yes")
is applied to a cell within a conditionally formatted range, the cell above will be
= ($B1="Yes")
and above that
= ($B1048576="Yes").
That is, conditional format references, like defined names, wrap from top to bottom of the sheet rather than throwing an error. The conditional format is still valid, if you went to the bottom of column B and typed Yes it would trigger the CF.
