Jul 10 2020 06:05 PM
Jul 10 2020 06:05 PM
When I use conditional formatting in excel to highlight cells that are duplicate, it works and creates a rule, but after a while it stops working while the rule is still active.
I can’t figure out why this happens. I searched online and cold not get an answer. Anyone has a clue?
BTW: Running Office 365 on a MacBook.
Thanks :)
Jul 10 2020 06:19 PM
@Deleted
Could you be a bit more precise and specific in your descriptions? I'll give you a few questions to start you off; there may be more.
Each of these questions gets at things that are possible causes. That's why it's important to get a more complete description, just as a doctor would want a more complete descripton of the pain you are experiencing, when it happens, where, the intensity, etc., before attempting a diagnosis.
Jul 10 2020 06:27 PM
@Deleted I'm kinda surprise that @mathetes didn't ask his favorite question ... can you please attach an example workbook for us to see. That said, my guess (because it happens to me all the time) is that you insert and delete rows/columns and that often causes issues with conditional formatting (the ranges get broken up or the rule gets split into multiple copies). But as mathetes said, we could really use more info to help you track it down.
Jul 10 2020 08:11 PM
@mathetes Yeah you're right, I will submit the info and as @mtarler said will upload an example workbook. I just created a new test workbook and wonder if I figured it out by accident.
I applied the rule before adding anything into the column and so far it works as it should. will use it and open and close a few times throughout today day and see if it sticks this time. If not, I'll upload the book and will clarify all the points you mentioned.
Fingers crossed :) and thanks for the replies!
Jul 11 2020 05:06 AM
@Deleted
You wrote: I applied the rule before adding anything into the column and so far it works as it should. will use it and open and close a few times throughout today day and see if it sticks this time.
I'm going to suggest that you reverse your thinking a bit here. You have been describing this from the beginning as a failure of Excel to consistently apply a conditional formatting rule for duplicate cells. Your most recent post (excerpted above) still talks of whether "it sticks this time."
The reality is that likelihood of Excel failing in this case is far, FAR lower, than is one of the other causes that @mtarler and I have alluded to.
So when this happens, don't think "why has Excel failed to see duplicates?" Instead, start your investigation with the mindset "Excel is telling me that these cells are not duplicates, even though they look to me as if they are; so let me see if I can figure out how they're not duplicates after all."