Conditional Formatting error

[Ronnie_Boone]

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.

[#2]

@Ronnie_Boone What is the applied range for the CF rule. If that is $B:$B you need to use =$B1="Yes"

[#3]

No, it's $A2:$G47. I used $B2 because the first row is headers.

[#4]

@Ronnie_Boone Have a look at the attached file. Do the CF rules look the same as yours?

[#5]

@Ronnie_Boone 

When you create a new rule for A2:G47, the active cell should be in row 2. I suspect that the active cell was in another row when you created the rule; that would explain the shift.

It's easiest to remove the rule and to create it again, this time making sure that the active cell is in row 2.

[#6]

@Ronnie_Boone 
is this same data you entered, because it works fine .

do you face it all workbook or this one is specific?
if it is specific then please upload sample data 

[#7]

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.

[#8]

@Ronnie_Boone 

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.

[#9]

@Ronnie_Boone 

We do understand your question; it's just that you didn't refer to the correct cell when you created the rule.

[#10]

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-ap...

(It's all to do with which cell is active when you enter relative cell values ie cell addresses with 1 or no $ signs)