Excel 2010 VBA Date Formula

Oz_Dex - Dec 8, 2011 at 07:36 AM
 dave - Sep 17, 2014 at 05:10 AM

I am trying to get a formula setup so when you click on a cell in excel it automatically enters the date. I am using the formula below which works however, I only want it to work on certain cells, ie. A1:A30, A35:A40, C15:C20 etc. Can anyone help me with my very limited VBA Skills. Formula is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = ActiveCell.Address Then

Target = Format(Now, "dd/mm/yyyy")

End If

End Sub.

Thanks a bunch :)

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 8, 2011 at 11:56 AM
Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim rngTestRange        As Range
   Set rngTestRange = Intersect(Target, Union(Range("A1:A30"), Range("A35:A40"), Range("C15:C20")))
   If Not rngTestRange Is Nothing Then
      rngTestRange = Format(Now, "dd/mm/yyyy")
   End If

End Sub
everywhere i go, always the drawn out answer.
how about the simple answer to how do you print a date in a cell with a format.. don't care which, here's one: yymmdd
all these fancy answers all over the internet. can't ever seem to get the base version of what looking for. where's the harm in including that.. a one liner for print a date to a cell. been trying to figure that out for a long time (aside from the 3 or 4 liner have for with me; yes i will include that..)

the starting point that have figured out for printing a date to a cell is:

Cells(ActiveCell.Row, E4).Value = now

where E4 is a work cell that shows eg col: CH
E4 has:

========== other print date example is:

If Not Intersect(Me.RANGE(D2), .Cells) Is Nothing Then
Application.EnableEvents = False 'EVENTS
With Me.Cells(.Row, E2) 'Destination
.NumberFormat = "mm"
.Value = now
End With
Application.EnableEvents = True 'EVENTS
End If