Excel VBA Mismatch Error

[TimberRaiders]

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!

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

 

[Teeroy]

Hi and welcome to the forum.

It sounds like the text in the Range "rdTimeStamp.Cells(j,1)" can't be evaluated by CDate as a date. Have you checked the text in this Range on the worksheet using the Datevalue function?

 

[TimberRaiders]

Hi Teeroy,

Thanks for the reply!

Thanks to some of the guys at Stack Exchange, I was actually able to realize what some of the issues were. My workbook was a "test" workbook, and every now and then i would clear the date values in the columns, thus throwing off the macro. I also realized how to make the workbook open only on open and not consistently.

One thing I am still not certain of how to do is to freeze the timestamp regardless of a change in the target cell. For example, C1 adds a time stamp if there is a data input (or change) in cell A1. However, I want to know how to Freeze the timestamp in C1 to only appear after a data input, NOT after a change (which can be accidental).

Thank you!