•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Does anyone know why my conditional formatting rules sometimes mess up and automatically change by themselves?
Our take
Have you ever experienced your conditional formatting rules in Excel suddenly changing on their own? If you’re finding that your defined range of A2:B200 is morphing into an unexpected string of cell references, you’re not alone. This frustrating issue can disrupt your workflow, especially when you rely on accurate data visualization. In this discussion, we’ll explore potential reasons behind these changes and share actionable strategies to prevent them in the future. Stay tuned to regain control over your formatting and enhance your Excel experience.
I have a rule that applies to A2:B200 however every few weeks I’ll see that the conditional formatting doesn’t get applied right and I’ll open the formatting rules and find it looks like “=A$21$B$67$B$63$B$69$A$71$A$72$A$74：$B$200” when it was set to “=$A$2:$B$200”.
Does anyone know why this happens and how to prevent it from happening in the future? I am using the latest version of office 365 and my document is saved in OneDrive.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What is my malfunction when trying to edit an existing formula rule in the Conditional Formatting menu?https://preview.redd.it/dnn2yluq06vg1.png?width=507&format=png&auto=webp&s=b825b8cc3b31f0640ef22acd35a806429d0d0d15 This has been an issue for me for years and I've never spent the time to figure out the issue. Not every time, but most of the time, when trying to edit the formula for a conditional formatting rule, it is very difficult to navigate around inside the input box - for instance, if I wanted to wrap the existing rule in an AND() function to add a second condition, sometimes I can just click the cursor at the beginning of the formula and it works fine, and sometimes I can use the arrow keys to move the cursor back and forth, but other times, clicking doesn't do anything to place the cursor, and using the arrow keys inserts a cell reference instead of moving the cursor. I've had this issue in the last several years' worth of Excel versions and across several different Windows 7, 10, and 11 machines. I'm hoping it's me being an idiot and not some vestigial Excel quirk. Thanks in advance for any advice! Edit: quick note that this happens whether I'm trying to edit the formula in the screenshotted view here, or if I click the arrow icon to break out the input box by itself. That doesn't seem to be a factor. submitted by /u/leostotch [link] [comments]
- Pasting data into excel changes the affected rows color under conditional formatting ruleshttps://preview.redd.it/fz2dwbwax2mg1.png?width=718&format=png&auto=webp&s=912a7b525462d62d8622910c70817bfbaa2d7772 Example above showing my issue. I have a conditional formatting setup to change row color based on input but when i paste the data that I want this to be affected by, it changes what cells are able to be changes by the input. Below is rule in its original form, but "applies to" changes upon pasting data. I would like the rule to change row color to be locked, and not based on what I paste, but I also just copy and pasted the rule from field F1 to the whole selection A2->F2, A3->F3 (etc) manually so I may not be applying it correctly. Thanks for your help. https://preview.redd.it/tqqmcc1qx2mg1.png?width=524&format=png&auto=webp&s=3fc9e292bae2b786dcd9980e5ca77419aaba799f submitted by /u/Ok_Living9137 [link] [comments]