Overwrite the value if other cell is not blank

[argandoz]

Hi! Guys,

Anyone can help me find a formula for :

Column A (Plan Date) and Column B (Date Recieved) contain date value. If B2 is blank, A2 will maintain the date entered in it. However if B2 has date (either earlier or later than A2) it will supersede the date with the date value in B2.


Thanks.

Argandoz

 

[mikerickson]

I'd use the Change event rather than SelectionChange

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$B$2" And Target.Value <> vbNullString Then
        Application.EnableEvents = False
        Target.Offset(0, -1).Value = Target.Value
        Application.EnableEvents = True
    End If
End Sub

 

[sndy211027]

This works great for two collumns but I am looking to use it for 6 columns can you creat similar code for that will step through the columns too?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long, i As Long
lastrow = Range("B" & Rows.Count).End(xlUp).Row
lastcolumn = Range("2" & Columns.Count).End(x1up).Column
For i = 2 To lastrow
For j = "B" To lastcolumn
If IsEmpty(Cells(i, j).Value) Then
Cells(i, 2).Value = Cells(i, 2).Value
Cells(i, "B").Value = Cells(i, "B").Value
Else
Cells(i, "B").Value = Cells(i, i).Value
Cells(i, 2).Value = Cells(i, j).Value
End If
Next i
Next j
End Sub

this is what I came up with but its giving me an error