Forum Discussion
Conditional Formatting error
Why is it that sometimes when I enter a formula in Conditional Formatting, Excel changes it when I save it? For instance, I just entered a formula to highlight all rows that have "Yes" in column B. My code entered was =$B2="Yes", but when I saved it, nothing was highlighted. When I looked at the code again, Excel had changed it to =$B1048541="Yes". Why does Excel do this? It's not the first time.
PS - This is the Microsoft 365 Apps for business version of Excel.
Ronnie_Boone What is the applied range for the CF rule. If that is $B:$B you need to use =$B1="Yes"
- No, it's $A2:$G47. I used $B2 because the first row is headers.
Ronnie_Boone Have a look at the attached file. Do the CF rules look the same as yours?
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.
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.
We do understand your question; it's just that you didn't refer to the correct cell when you created the rule.
- 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)
