vbscript - I need to change the cell color in excel with a timer -

vbscript - I need to change the cell color in excel with a timer -

i have script monitors folders , putting results in excel file. there method when cell has been changed after 30 minuts gets greenish color gets? , if cell has not been changed after 30 minutes reddish color?

i forgot mention want check multiple cells.

here picture. under f

i hope clear since english language not good. laatste import(last import) needs checked

my code:

'===== const advarchar = 200 const addate = 7 const adbigint = 20 '============================================================================== 'set objecten set wshshell = wscript.createobject("wscript.shell") set fso = createobject("scripting.filesystemobject") set objpadimport = fso.getfolder("\\netko-sbs\data\imports\") set subfolderimport = objpadimport.subfolders excelbestand = "\\netko-sbs\data\imports\output.xlsx" set objfile = fso.opentextfile("c:\users\karim\desktop\vbscripttest\importv3\lokaties.txt", forreading) 'waarden const forreading = 1 dim arrfilelines() = 0 until objfile.atendofstream redim preserve arrfilelines(i) arrfilelines(i) = objfile.readline = + 1 loop objfile.close '============================================================================== 'wscript.sleep 10000 'sleeps 10 seconds '============================================================================== 'create custom disconnected recordset 'with fields filename , lastly modified date. '============================================================================== 'record set maken '============================================================================== set rs = createobject("ador.recordset") rs.fields.append "foldername",advarchar,255 rs.fields.append "moddate",addate rs.fields.append "naam",advarchar,255 rs.fields.append "tijd", advarchar,20 '============================================================================== 'excel set objexcel = createobject("excel.application") objexcel.displayalerts = false 'foutmeldingen uitschakelen set objworkbook = objexcel.workbooks.add() 'bestand openen.. 'objworkbook.saveas(excelbestand) objexcel.visible = true 'toon excel objexcel.cells(1, 1).value = "foldernaam" 'header instellen objexcel.cells(1, 2).value = "laatste import" 'header instellen objexcel.cells(1, 3).value = "controle tijd" 'header instellen x = 2 'set de juiste rij in excel. '============================================================================== rs.open '===== 'load file name, date, etc. (mapen controleren) '============================================================================== '============================================================================== each strline in arrfilelines s = split( strline, "," ) set folder = fso.getfolder( s(0) ) 'set test = (folder.datelastmodified - s(2)) rs.addnew array("foldername","moddate", "naam", "tijd"), _ array(folder.name,folder.datelastmodified, s(1), s(2)) ',test) rs.update next s = "sortering van oud naar nieuw:" & vbcrlf _ & "=============================" & vbcrlf if not (rs.bof , rs.eof) rs.sort = "moddate asc" rs.movefirst until rs.eof objexcel.cells(x, 1).value = _ rs.fields("naam").value objexcel.cells(x, 2).value = _ rs.fields("moddate").value objexcel.cells(x, 3).value = _ rs.fields("tijd").value x = x + 1 rs.movenext loop end if 'excel set objrange = objexcel.range("a1") 'selecteer actieve cell objrange.activate 'activeer cell set objrange = objexcel.activecell.entirecolumn objrange.autofit() 'set grootte van kolom set objrange = objexcel.range("b1") 'selecteer actieve cell objrange.activate 'activeer cell set objrange = objexcel.activecell.entirecolumn objrange.autofit() 'set grootte van kolom set objrange = objexcel.range("c1") 'selecteer actieve cell objrange.activate 'activeer cell set objrange = objexcel.activecell.entirecolumn objrange.autofit() 'set grootte van kolom '============================================================================== vartype moddate = objexcel.cells(1, 1).value = "laatste import" if datediff("n",moddate,date) < 30 objexcel.cells(y,y).interior.colorindex = 3 else objexcel.cells(1,1).interior.colorindex = 4 end if '============================================================================== objworkbook.saveas(excelbestand) 'excel bestand opslaan 'objexcel.quit 'excel afsluiten als nodig is. '============================================================================== '============================================================================== 'objfile.writeline s 'schrijf waarden naar excel set rs = nil 'gooi rs leeg set folder = nil 'object leegmaken set fso = nil 'object leegmaken set objpadimport = nil set objpadfrigo = nil set subfolderfrigo = nil set objexcel = nil '==============================================================================

getlastmodified time of cell store in 1 of cell or in variable.

for illustration : if store time in 1 of cell then,

var lastmodifitime = objexcel.cells(x,1).value

'if cell value modified in lastly 30 minutes set red, else if not modified in lastly 30 minutes or more set background color greenish

if datediff("n",lastmodifitime,date) < 30 objexcel.cells(y,y).interior.colorindex = 3 else objexcel.cells(z,z).interior.colorindex = 4 end if

' link help lastly modified time : http://www.online-tech-tips.com/ms-office-tips/track-changes-in-excel/

excel-vba vbscript