Excel date format VBA

Question

I set the value of a cell to the current date using the following code

rng.Value = Format(Now(), "dd/mm/yyyy")
rng.NumberFormat = "dd/mm/yyyy"

When I run this code the date is inserted in to the cell and the format is "Date" but the data in the cell is left aligned. Formulas don't interpret the value as a date.

When I click into the cell and then press ENTER the value shifts to the right and the date now works with the formulas.

If I enter the date manually the formulas also work. It is only when I use VBA.

Answer 1

Don't format the input, format the cell (and you can just use Date):

rng.Value = Date
rng.NumberFormat = "dd/mm/yyyy"

Answer 2

Two remarks. 1. You are in fact inserting both date and time (that is what the NOW function returns). If you only need the date, use the VBA Date function instead. 2. As ashleedawg already mentioned in the comments, Format returns a string. Change your code to:

rng.Value = Date
rng.NumberFormat = "dd/mm/yyyy"