Highlight Cells Containing Duplicate Document Codes In Commaed Lists

I've been trying to find how to mark duplicate product codes. The excel file is for recording document changes. Each row contains the data relavent to the change. One column represents the documents affected by the change. The documents all have differen't naming standards and are listed in the cell like this "XHD89d, 88-0345b, NO45.KDg". The last letter represents the revision of the document. I'm wanting excel to flag cells if one of the document codes is the same as in another cell within the column. Ie one cell contains "45-9930j, FF55.JKb" and another "45-9930j, 33-0989h" so the cells would be highlighted indicating that another person has associated that document revision with their change. This is to avoid two people working on the same document at once. Once the change has been approved the document revision is updated so the next person wouldn't input the same value, ie 45-9930j -> 45-9930k. I've found some things about looking at each cell but not if the cell contains some comma separted list. Any ideas?

Select the range of interest and run this macro. Paste the code into a new module in the VBA editor (ALT F11)

Please Login or Register  to view this content.

Any cells containing duplicates will be highlighted in red.

Thanks that's what I was looking for.
Though it needs to check both cells with comma separated items and ones with only one item, ie no commas. Also I'd like this to run anytime a cell in the particular column is changed. I tried figuring this out with worksheet events and something that would select cells 1-99999 in a particular column but I couldn't figure it out. The macro seemed to crash if I selected the whole column I assume because that's infinite # of cells or something like that.

The code should work for any mixture of cells containing comma or not.

The reason for your 'crash' with the whole column is that the macro compares every cell with every other cell to look for duplicates and therefore the length of time taken to run the code is roughly proportional to the square of the number of cells selected. Selecting the whole column means a lot of redundant activity.

It would be possible to produce a slightly more sophisticated version which selected only the filled cells within the range which would get around such an issue.