excel - Run Time Error 13 - Mismatch on Date -

avid reader, first time poster here. have macro obtained internet part, made adjustments. it's purpose color code cells have passed duration. working fine earlier, getting error on "type mismatch". line reads "this error is" getting mismatch. puzzled because working fine earlier. not seasoned programmer means, try troubleshoot things. have looked on net , cant find specific answer question.

in addition, if of willing, appreciate advice on how make code run @ startup of workbook , not periodically set now.this code not placed in worksheet, in module.i mention because not sure how of practical difference can make appreciated, thanks!

 public timetorun date  sub auto_open() call schedulecomparetime   end sub  sub schedulecomparetime() timetorun = + timevalue("00:00:10") application.ontime timetorun, "comparetimestamp"   end sub  sub comparetimestamp() dim rgtimestamp range dim rdtimestamp range dim long dim j long dim mynow date dim timestamp date, timestampp date  set rgtimestamp = range("c1:c500") set rdtimestamp = range("h1:h500")  = 1 rgtimestamp.rows.count      if not rgtimestamp.cells(i, 1) < 1 'don't run empty cell          mynow = cdate(now - timeserial(0, 0, 0)) 'time instantly         timestamp = cdate(rgtimestamp.cells(i, 1)) 'this error is!!         if timestamp < mynow 'if it's old @             rgtimestamp.cells(i, 1).interior.colorindex = 3 'make fill colour red         end if     end if    next  j = 1 rdtimestamp.rows.count      if not rdtimestamp.cells(j, 1) < 1          mynow = cdate(now - timeserial(0, 0, 0))         timestampp = cdate(rdtimestamp.cells(j, 1))         if timestampp < mynow             rdtimestamp.cells(j, 1).interior.colorindex = 3         end if       end if 'closes if not next call schedulecomparetime  'begins scheduler again end sub  sub auto_close() 'turn scheduler off can close workbook application.ontime timetorun, "comparetimestamp", , false end sub 

you have data in 1 or more cells excel cannot convert date. can around adding simple checking such this:

'.... beginning of code  if not rgtimestamp.cells(i, 1) < 1 'don't run empty cell      mynow = cdate(now - timeserial(0, 0, 0)) 'time instantly      if isdate(rgtimestamp.cells(i, 1)) = false         msgbox "invalid date found in cell " & rgtimestamp.cells(i, 1).address(false, false)         exit sub     end if      timestamp = cdate(rgtimestamp.cells(i, 1)) 'this error is!!     if timestamp < mynow 'if it's old @         rgtimestamp.cells(i, 1).interior.colorindex = 3 'make fill colour red     end if end if   '... rest of code 

if want code run @ startup change sub auto_open this:

sub auto_open() call comparetimestamp end sub