VBA formula to copy format and paste cells for dates?

[ThePangloss]

I've got this code

[/FONT][FONT=Courier New]Sub Datar()
Dim sel As Range, ass1start As Range, ass1end As Range 
Dim ass2start As Range, ass2end As Range
Dim temp1 As Range, temp2 As RangeSet 
sel = Selection
For Each Row In sel.Rows
 Set ass1start = Row.Cells(1, 1)
 Set ass1end = Row.Cells(1, 2)
 Set ass2start = Row.Cells(1, 3)
 Set ass2end = Row.Cells(1, 4)
 If IsEmpty(ass2start) Or IsEmpty(ass2end) Then
 'next ElseIf ass1start = ass2start And ass1end > ass2end Then 
ass2end.Copy Row.Cells(1, 2) 
ass1end.Copy Row.Cells(1, 4) 
ElseIf ass1start > ass2start Then 
' ass1start.Copy Row.Cells(1, 3) = temp1 
' ass1end.Copy Row.Cells(1, 4) = temp2 
Set temp1 = Row.Cells(1, 1) 
Set temp2 = Row.Cells(1, 2) 
ass2start.Copy Row.Cells(1, 1) 
ass2end.Copy Row.Cells(1, 2) 
temp1.Copy Row.Cells(1, 3) 
temp2.Copy Row.Cells(1, 4) 
End 
IfNext
End Sub
[/FONT][FONT=Courier New]

The problem is, it's supposed to switch ass1start and ass2start and ass1end and ass2end, but instead it pastes both ass2start and ass2end into both.

So lets say I have this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assignment 1 start[/TD]
[TD]Assignment 1 end[/TD]
[TD]Assignment 2 Start[/TD]
[TD]Assignment 2 end[/TD]
[/TR]
[TR]
[TD]1/10/2007[/TD]
[TD]2/12/2008[/TD]
[TD]5/6/2005[/TD]
[TD]1/9/2006[/TD]
[/TR]
[TR]
[TD]1/20/2008[/TD]
[TD]2/20/2009[/TD]
[TD]2/21/2009[/TD]
[TD]2/27/2009[/TD]
[/TR]
</tbody>[/TABLE]


What it should do is take the two dates that are before the first two, (5/6/2005 and 1/9/2006 and replace them with 1/10/2007 and 2/12/2008 and then put 5/6/2005 and 1/9/2006 into the first two cells of the row.)
The second row is fine because they're all chronologically placed.

Instead I get something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assignment 1 start[/TD]
[TD]Assignment 1 end[/TD]
[TD]Assignment 2 Start[/TD]
[TD]Assignment 2 end[/TD]
[/TR]
[TR]
[TD]5/6/2005[/TD]
[TD]1/9/2006[/TD]
[TD]5/6/2005[/TD]
[TD]1/9/2006[/TD]
[/TR]
[TR]
[TD]1/20/2008[/TD]
[TD]2/20/2009[/TD]
[TD]2/21/2009[/TD]
[TD]2/27/2009[/TD]
[/TR]
</tbody>[/TABLE]

where it just copied the last two and didn't paste the first two inplace of the last two. Any ideas? I'd like to preserve formatting of the cells as well because the colors I have on the cells help me differentiate assignments from different contractors. So assignment 1 start and end might be green, and assignment 2 start and end might be blue. I'd like to keep them those colors when switched.

Thanks in advance.

 

[StephenCrump]

Here's the problem:

Set temp1 = Row.Cells(1, 1)
ass2start.Copy Row.Cells(1, 1)  'temp1.Value has just been overwritten!
temp1.Copy Row.Cells(1, 3)

Here's one way you could modify your code:

Dim dteTemp1 As Date

dteTemp1 = Row.Cells(1, 1).Value
ass2start.Copy Row.Cells(1, 1)
ass2start.Value = dteTemp1