Excel Conditional formatting not working Optimally..Bug?

Question

So i created a conditional formatting rule to check for duplicate values in one column of my sheet. It is a shared workbook as i used it with my colleagues. Been working fine since 8 months ago but suddenly this month, it started acting up. Like I'd literally write the same values 2 times in the column and it would not highlight it but it does for some. Like the conditional formatting will still be there bit not optimal. For instance, i might have 0.101 in the 2nd row of the table and then go down to like row 964 and still write same 0.101 and nothing will be highlighted but if i come down below to row 965, it gets highlighted meaning the conditional formatting still works. So i don't know what's wrong

Answer 1

I am having the same problem. I was going through and resolving the duplicates (about 500 in 70,000 rows) manually. The highlighting VANISHED entirely. (This has happened before over the past couple years). It's simple to fix normally. I clear all formatting from the sheet and then re-do it and it comes back.

This time it won't come back. There is no way I can find these previously highlighted cells without highlighting them. I've quit Excel and restarted and everything.

Answer 2

Well the applied range is the whole colum in the sheet. I select this by highlighting it from the column header while omitting the header itself but it selects the whole column and them I apply the CF

For more context, like I said earlier I share the sheet with 5 more people,

I paste content on the values on the column but this values have no formula.

And no, no row has been deleted or inserted. Also I can't share the sheet rn cause it works..for now

I have to reset the formatting ie add another duplicate CF for it to work but after a while. It starts malfunctioning again

Answer 3

It's the applied range of the CF and/or the formula inside the CF uses a relative reference and becomes wrong.

Such things can happen if someone deletes / inserted rows. That's no bug, the behavior is normal / by design / just unexpected.

If you need further help we need to see the file.

Andreas.