Please help me finish this simple macro

[Arlen]

Hello, Everyone!

I am trying to convert text dates to real dates. I need to find all yellow
cells in range B4:B100, strip off the offending text (i.e. Sat. or Thurs),
and use DateValue to turn the rest (which looks like 1/05/2007, but sometimes
1/05/07) into a proper, numerical date in the ddd mm/dd/yy format, arial,
bold, 10pt, and centered in place.

I've spent days trying to write this macro. I've had excellent help from
people on here, but whenever I need to make any slight modification, a
trainwreck ensues. Please see the code below.

Sub switchDate()
Dim MyDate As String
For Each cell In ActiveSheet.Range("B4:B100")
If cell.Interior.ColorIndext <> xlNone Then
cell.Value = MyDate.Value
MyDate = Trim(MyDate)
If InStr(MyDate, " ") > 0 Then
MyDate = Mid(MyDate, InStr(MyDate, " ") + 1)
End If
MyDate = DateValue(MyDate)
End If
Next
End Sub

This doesn't even begin to work and I can't begin to guess why. Could
somebody out there please make the tweaks and tell me what I did wrong, that
I might not do it hence?

I thank you greatly, and have a terrific day.

Arlen

 

[ML0940]

I haven't tried it yet but if you turn on the macrro recorder, then format
the string date to a real date format, manaully, I can almost guarantee that
your code will be recorded.

ML

 

[ML0940]

Ok, I just tried that.
This is what I got

Range("Cell").Select 'or Activecell.Select
Selection.NumberFormat = "m/d/yy;@"

Is that what you are looking for?

ML

 

[ML0940]

Hi Arlen
If you don't mind me asking; why did your date format start out as a String?
Are you retreiving a date from a user?

ML

 

[Nigel]

This should do it......

Sub switchDate()
Dim cell As Range
For Each cell In ActiveSheet.Range("B4:B100")
If cell.Interior.ColorIndex <> xlNone And InStr(Trim(cell.Value), " ") > 0
Then
With cell
.Value = Format(Mid(cell.Value, InStr(cell.Value, " ") + 1), "ddd
mm/dd/yy")
.HorizontalAlignment = xlCenter
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
End If
Next
End Sub