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

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"

From stackoverflow

• 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
  

  Shannon : That makes sense and seems to work out! Thanks!!

• Selecting the cells before changing their value is unnecessary, and FormulaR1C1 is superfluous aswell as you are not entering a range in the formula. The following should do exactly the same, but is I think easier to follow and debug.

  ** Same caveat as Jim about the offsets, they look a bit odd but might be what is intended **

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

  Explicitly declaring the type of your variables as Date is also a good idea, as they are otherwise stored internally as Variant data type which require more memory and can lead to unexpected consequences as Excel is left with the responsibility of deciding how to treat the data.

  
      Dim ActualStartDate as Date
      Dim ProjectedStartDate as Date
  
      ActualStartDate = ActiveCell.Offset(-1, -1)
      ProjectedStartDate = ActiveCell.Offset(1, 0)
  
      If ActualStartDate = ProjectedStartDate Then
          ActiveCell.Offset(-1, -1).Interior.Color = RGB(0, 0, 255)
      End If