Excel 2010 VBA Date Formula

Solved/Closed
Report
-
 dave -
Hi,

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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CH4),"$",""),ROW(),"")



========== 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