TimberRaiders
New Member
- Joined
- May 30, 2015
- Messages
- 2
Hi Guys,
I posted basically the same exact thing as you see below on StackOverflow but I didnt really get any help. I think those guys are for more serious programmers, and I am very entry level. I actually found the code below on MrExcel forum and just adjusted it. I am hoping someone can help me with it. Basically, I have a column where the cell values are the "Now" function If the parallel cell in column A is filled. If it is filled, it time stamps it. The Macro I got online colors the TimeStamp cell if it has reached a certain age. I have quite a few things I need help with this Macro, among them is:
Getting the Macro to only run at Startup
Fixing the mismatch error
potentially FREEZING the time stamp so once it is placed in the cell, it doesnt always update.
I would really appreciate any help from you guys. I am a believer in the "teach a man to fish philosophy", so while I want an answer for sure, I would really appreciate if you guys could help me understand the fix better. Thank you!
EDIT: Should have been clearer, the mismatch error is a type 13 and it involves the TimeStamp as Date, I believe, Thanks!
I posted basically the same exact thing as you see below on StackOverflow but I didnt really get any help. I think those guys are for more serious programmers, and I am very entry level. I actually found the code below on MrExcel forum and just adjusted it. I am hoping someone can help me with it. Basically, I have a column where the cell values are the "Now" function If the parallel cell in column A is filled. If it is filled, it time stamps it. The Macro I got online colors the TimeStamp cell if it has reached a certain age. I have quite a few things I need help with this Macro, among them is:
Getting the Macro to only run at Startup
Fixing the mismatch error
potentially FREEZING the time stamp so once it is placed in the cell, it doesnt always update.
I would really appreciate any help from you guys. I am a believer in the "teach a man to fish philosophy", so while I want an answer for sure, I would really appreciate if you guys could help me understand the fix better. Thank you!
EDIT: Should have been clearer, the mismatch error is a type 13 and it involves the TimeStamp as Date, I believe, Thanks!
Code:
Public TimeToRun As Date
Sub Auto_Open()
Call ScheduleCompareTime
End Sub
Sub ScheduleCompareTime()
TimeToRun = Now + TimeValue("00:00:10")
Application.OnTime TimeToRun,"CompareTimeStamp"
End Sub
Sub CompareTimeStamp()
Dim rgTimeStamp As Range
Dim rdTimeStamp As Range
Dim i As Long
Dim j As Long
Dim MyNow As Date
Dim TimeStamp As Date, TimeStampp As Date
Set rgTimeStamp = Range("c1:c500")
Set rdTimeStamp = Range("H1:h500")
For i =1 To rgTimeStamp.Rows.Count
If Not rgTimeStamp.Cells(i,1) < 1 Then 'don't run for an empty cell
MyNow =CDate(Now - TimeSerial(0,0,0))'time instantly
TimeStamp =CDate(rgTimeStamp.Cells(i,1))'THIS IS WHERE THE ERROR IS!!
If TimeStamp < MyNow Then'if it's old at all
rgTimeStamp.Cells(i,1).Interior.ColorIndex = 3 'make fill colour red
End If
End If
Next
For j =1 To rdTimeStamp.Rows.Count
If Not rdTimeStamp.Cells(j,1)<1Then
MyNow =CDate(Now - TimeSerial(0,0,0))
TimeStampp =CDate(rdTimeStamp.Cells(j,1))
If TimeStampp < MyNow Then
rdTimeStamp.Cells(j,1).Interior.ColorIndex =3
End If
End If'closes If Not
Next
Call ScheduleCompareTime 'begins the scheduler again
End Sub
Sub auto_close()'turn the scheduler off so you can close workbook
Application.OnTime TimeToRun,"CompareTimeStamp",,False
End Sub
Last edited by a moderator: