Hi All, anyone a pro with macros in excel? I'm creating a checklist with a check box next to each task that when selected grays out the row and adds the date it was completed. Problem is if I use the formula "=Today()" for the macro the spreadsheet changes every day. I tried recording the macro using the shortcut of "crtl + ;" but that only records the date it was inputted, so when I copy the macro to the next task, it just copies today's date, not the input.

Basically, I'm trying to figure out a way to time stamp in the macro and not have it change. Macro is below. The bold line is where I'm trying to insert the time stamp:

Sub Macro2()
'
' Macro1 Macro
'

'
Range("B5:L5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With
Range("L5").Select
ActiveCell.FormulaR1C1 = "4/28/2017"
Range("B6").Select
End Sub

3 ...

Report Post

Posted by LSUtigerME

Walker, LA

Member since Oct 2012

3796 posts

Posted on 4/28/17 at 3:44 pm to Furbs311

Just insert the date as a value, not a formula. Use VBA to determine the date/time, and insert that value, not the formula, into a date formatted cell.

I can help with the syntax later, but you should be able to get it this way.

ETA: quick google
Range("A1") = Format(Date, "mm-dd-yyyy")

This post was edited on 4/28/17 at 3:46 pm

0 ...

Report Post

Posted by mdomingue

Lafayette, LA

Member since Nov 2010

30304 posts

Posted on 4/28/17 at 5:08 pm to Furbs311

ActiveCell.FormulaR1C1 = FormatDateTime(Date,vbShortDate)

Or you could use this if you are alway putting it in the current active cell

ActiveCell.Value = FormatDateTime(Date,vbShortDate)

1 ...

Report Post

Posted by Furbs311

South Carolina

Member since Oct 2005

516 posts

Posted on 5/1/17 at 8:23 am to mdomingue

Exactly what I was trying to do, thanks!

0 ...

Report Post

Posted by Footbaw

Fulshear, TX

Member since Oct 2015

424 posts

Posted on 5/1/17 at 2:55 pm to Furbs311

Man. I really hate how the macro recorder does things.

All of that code can be reduced to something like this:

quote:
Option Explicit

Sub Whatever()

With Range("B5:L5")
.Interior.ColorIndex = 48
.Font.Color = vbBlack
End With

Range("L5").Value = Format(Now(), "mm/dd/yyyy hh:mm:ss")

End Sub