Excel VBA Mismatch Error


New Member
May 30, 2015
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


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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?
Upvote 0
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!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back