I am seeing some odd things with conditional formatting for duplicate values.
I have a sheet where I have a conditional format to check for duplicate values and it seems to work. However, the values are actually the product of a formula and not directly entered into the cell.
Let’s say I have the duplicate rule applied to column A and I have A1=10 and A2 = 11. I want to swap those two values so i go to A1 and Input 11. This is now marked as a duplicate because it is there twice. When I go to A2 and input 10, A1 is no longer marked as a duplicate.
However, if I apply the same duplicate value check to a column that is a product of a VLOOKUP function, the only cells that are evaluated for the conditional formatting are those cells that were directly affected by the change that I made.
In the attached workbook, on sheet1, column B is a simple Vlookup of column A in the table just to the right and column B has a conditional formatting rule checking for duplicates. If you change A1 to 1, cell B1 changes to True and gets the conditional formatting for being a duplicate value. However, B2, even though it too is True, does not get the conditional formatting. If you go into cell A2 and re-enter 1 (or double click on the cell) and hit enter, then it gets the conditional formatting as being a duplicate.
So, now B1 and B2 are “True” and have the conditional formatting for duplicates. If I go into A2 and enter 0, cell B2 becomes False and is no longer formatted as being a duplicate. However, cell B1 is still marked as being a duplicate, even though it no longer is a duplicate. I have to go back into A1 and enter 1 in order for B1 to no longer be marked as a duplicate.
Now, here’s the really, REALLY freaky part…Sheet2 is a direct copy of sheet1 except it also has a conditional formatting rule for duplicates applied to column A. On that sheet, whenver you change a value in A, everything is updated correctly with the conditional formattin of column B!!! If you enter 1 into A1, B1 changes to Ture and both B1 and B2 are maked as duplicate. If you then go into A2 and enter 0, both B1 and B2 are no longer marked as duplicates.
Why does the value change in column A cause the conditional formatting of Column B to be completely re-evaluated when it has conditional formatting of it’s own but when when it doesn’t have conditional formatting only modified cells in column B are re-evaluated for conditional formatting? That doesn’t make a whole lot of sense. I think the re-evaluation of the conditional formatting of column B should be re-evaluated if any of the vales have changed.
Example.ods (9.0 KB)
