Assigning a cell a variable without deleting it's original contents...

Question

Hi, I'm setting up some VBA in excel to compare two dates to see if they're the same. The problem I'm having is that after assigning the cell's variable to "ActualStartDate", it deletes everything from the cell. So "ActualStartDate" is assigned correctly, but deleted at the same time.

Here's my code, any help would be fantastic. (this activecell stuff is because it's all relative references)

Dim ActualStartDate, ProjectedStartDate
ActiveCell.Offset(-1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = ActualStartDate
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = ProjectedStartDate
If ActualStartDate = ProjectedStartDate Then
    ActiveCell.Offset(-1, -1).Range("A1").Interior.Color = RGB(0, 0, 255)
End If

When it runs, it DOES recogize my test dates to be the same and color the cell. It just deletes the content of the cell that contains "ActualStartDate"

Answer 1

All this is doing is moving one cell up, one cell to the left, and replacing that cell's value with nothing.

Dim ActualStartDate
ActiveCell.Offset(-1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = ActualStartDate

You create the ActualStartDate variable, but never assign anything to it. So, when you apply the ActualStartDate variables value to a cell, it "deletes" the existing date.

You probably want to do something more like this, though I'm not sure about the Offsets because I don't know how you've designed your spreadsheet.

Sub Button1_Click()
    Dim ActualStartDate, ProjectedStartDate
    ActiveCell.Offset(-1, -1).Select
    ActualStartDate = ActiveCell.FormulaR1C1
    ActiveCell.Offset(1, 0).Select
    ProjectedStartDate = ActiveCell.FormulaR1C1
    If ActualStartDate = ProjectedStartDate Then
        ActiveCell.Offset(-1, -1).Interior.Color = RGB(0, 0, 255)
    End If
End Sub