Conditonal formatting based off date&time

[crizzle]

thanks to the great help of daddylonglegs i've been able to take my julian date/time in column 'D' and apply it as a standard date/time format that excel understands in column 'BL'

what i would like to have happen is when the date/time in column 'BL' is 20 minutes from expiring (based off local computers time) for it to fill in the corresponding cell in column 'D' as 'yellow'

and when the time has expired to fill the corresponding cell in column 'd' as 'red'

is this possible either as a formula or in vba? Im using excel 2007

[crizzle]

is it safe to assume that this cant be done then? any advice would be appreciated. TIA

[daddylonglegs]

You can do this with conditional formatting like this: [revised]

Select whole column D and Format > Conditional Formatting > use "formula is" option and set these conditions

condition 1
=(NOW()>BL1)*(BL1<>"")
format red

condition2
=(NOW()+"0:20">=BL1)*(BL1<>"")
format yellow

...however this won't update automatically as time passes so you might need some VBA to do that. This code will put a clock on your spreadsheet and also update the conditional formatting automatically.

[crizzle]

sorry for the late reply, i'm living in korea at the moment and working some 'strange' hours to say the least.

anyways, so i added the conditional formatting, so thats done. now as for the time the code in the link you gave me was

Please Login or Register  to view this content.

now heres a really newb question, the part of that code that says 'range(c3) and (c4) do i need to change those to the appropriate blocks in my spreadsheet or is it unrelated? i feel pretty stupid asking that, since i think i know the answer...

the next question... with this macro, will i need to have a button to press on the sheet somewhere? (thats cool if its the case) or can i just add the code to the sheet and it will run automatically?

[crizzle]

well... i got it working kinda...
problem is i have a macro that updates this page with info from the other sheets in the workbook.

Please Login or Register  to view this content.

how can i make it so this will only refresh the results from column a-j rows 1-31? once thats settled i know how to finish everything since i disabled that macro and the conditional formatting works great. =)