changing color of strings of text in multiple cells at once

[jan001]

I have a worksheet I produce weekly that shows projects assigned to each employee and each project's status and some other stuff. I export it from the db into Excel and sort it by employee name. Then each employee name is color coded (in a column by itself), along with the date of the last update (at the start of a different column).

Changing the employee names in bulk is a snap, but I'm trying to figure out whether there's a better way to change the dates' color, which is supposed to match the employee name color, to make it easier for TPTB to do an at-a-glance thing.

For instance:

Employee  Update
Smith       06/30/11 LS no progress
Jones       7-2-11 JRJ meetings next week
Williams    6/15/2011 pending budget dtw approval

You'll see that the date formats are all different. That's because, despite my preaching and pleading, each employee insists on entering the date of his/her update however he/she **** well pleases. The letters after each date are the initials of the employee who last updated the record. You can see some use three initials, some use two.

This means I have to highlight each date and change the color. It's not really THAT much data, only about 85-90 records, and I can zip through it pretty quickly, but if there's a way to do it faster...? Like maybe a way to define a conditional format based on all the characters before the second blank space...?

Disclosure of ulterior motive: I've been told this particular report might be lifted from my list of things to do and assigned to someone else, which is FINE.WITH.ME. I'm just trying to think of a way to make it a little more elegant/streamlined, to try to avoid any "but that takes too long" objections.

Any thoughts?

 

[Darkspartan]

Would you be against seperating the data into diff columns?

Date Initial Comment

 

[jan001]

I've thought of that (presuming you mean text-to-columns). In fact, I do use that on another report, to separate and then sort projects by their age based on their unique numeric identifier within the project name.

But this is for someone (the person I've been told would take this over) who, as far as I see, is only used to using Excel for tracking invoices, etc., so I'm trying to find something that will be a little less intimidating. That's why I was wondering whether there's a way to tell conditional formatting to only take place prior to the second blank space.

 

[Rick Rothstein]

Let me see if I understand... you have names in Column A and those names are colored and you want to make the date part of the text in Column B the same color. If that is correct, then give this macro a try...

Sub ColorDates()
  Dim X As Long, LastRow
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow To LastRow
    Cells(X, "B").Characters(1, InStr(Cells(X, "B").Value, " ") - 1).Font.ColorIndex = Cells(X, "A").Font.ColorIndex
  Next
End Sub