Unfortunately, just because I have set up some websites for people and fixed some Access and Excel problems for people here at work, I am someone people come to when they don't want to charge time to IT etc. Normally I don't mind too much and it is usually stuff that is well within my grasp (but I am certainly not advanced in either Access or Excel as you will see by my question heh). I usually enjoy taking the time to solve/learn to solve the problem myself when I have time. The problem right now is that I don't have the time to figure out how to do this. I'm hoping someone on here can look at it and know pretty quickly. If not, I'll tell them they need to talk to someone else who holds this responsibility, no biggie.

Problem/Request:

Someone in our HR has a very large spreadsheet for all of the employees in a certain department at this office location. It has lots and lots of columns, but only 2 really matter: the employee ID # column and the hiring/start date. Because we are project based, work ramps up and down and some people have been let go then rehired more than once, each providing a new entry into this list. Each entry will have the same ID # but a different date.

I have made a very simplified sample version of what I saw with dummy data (I don't have actual access to the spreadsheet in question), just for a visual. Obviously the real spreadsheet has tons more rows and columns, but:



They are wanting to identify duplicates (which by itself is obviously easy to do with countif) in the ID # column, but then look at the hiring date and ONLY highlight the row with most recent date. In my example, only 1 person was duplicated and I manually showed the 1 row that has the most recent date attached to the duplicated ID # as an example of their desired outcome.

What is the most simplified macro to check/highlight the cells that fit the criteria whenever they open the shreadsheet?

11/11/2009 1:38:40 PM