(newbie) How can I flag multiple values in a column?

Dave Navarro


Hello all -

I'm trying to create an Excel VBA macro (my first one!) that will do the following:

1. Search a column, and for any duplicate values in that column, turn those cells red
2. Move to the next column and repeat (flagging values that are duplicates *only* in that column).
3. (Preferably) Quit when it comes to an empty column.
   

The XLS in question is a scheduler where the columns represent weeks, and the rows are dev projects with the developers listed below them. We have about a dozen projects, so it doesn't all fit on a screen, and it's hard to tell where we ar double booking people. This macro would turn any people who are double booked (i.e., listed in a column more than once) into cells with red backgrounds.

I've found a macro which will find and flag duplicates (http://www.rondebruin.nl/find.htm), and I've tried to tweak it, but essentially I'd have to duplicate the code over and over again for each column (I'm a tester, not a coder, so I don't know how to make it jump from column to column, caring only about the values in the column in question).

Could anyone wiser and more experienced than I Help me out here

Shasur

You can know the empyt column by

lLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).column

if lLastRow will give the last column that contains value

Here is the code for detecting dupes

http://vbadud.blogspot.com/2007/08/detecting-duplicate-values-excel-vba.html

Hope it helps

Cheers

Shasur