Excel 2010 VBA Date FormulaSolved/Closed

Question

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 :)

rizvisa1 · Accepted Answer

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

dave · Answer

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

dave · Answer

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

Excel 2010 VBA Date Formula

3 responses

Similar discussions

Subscribe To Our Newsletter!