Forum Discussion

Ronnie_Boone's avatar
Ronnie_Boone
Copper Contributor
Mar 17, 2022

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's avatar
    Ronnie_Boone
    Copper Contributor

    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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      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.

Resources