Forum Discussion
Excel conditional formatting for duplicate values
I don't know for sure, but my guess is that conditional formatting is using here same algorithm as COUNTIF(), i.e. converts texts which could be converted to numbers to them and perform number comparison.
The workaround is the same - use rule with the formula, with SUMPRODUCT() or like COUNTIF(<range>, <value>&"*").
E.g if part numbers in column E the rule formula could be
=(COUNTIF($E:$E,E1&"*")-1)*(E1<>"")
I don't know for sure, but my guess is that conditional formatting is using here same algorithm as COUNTIF(), i.e. converts texts which could be converted to numbers to them and perform number comparison.
The workaround is the same - use rule with the formula, with SUMPRODUCT() or like COUNTIF(<range>, <value>&"*").
E.g if part numbers in column E the rule formula could be
=(COUNTIF($E:$E,E1&"*")-1)*(E1<>"")
Thanks, it works!
To Subodh_Tiwari_sktneer, your solution works too, but in my sample file I forgot that the range I've used in my "real" file was for the complete column ($A:$A), and when I've applied your formula all the blank cell had then got the formatting...