Conditional Formatting

to do that you need to have a counter which the final user will not be able to see. one option is creating a very hidden sheet (in developer enviroment by changing sheet visibility property to very hidden) and add a counter to a cell and use the same sheet for other settings or a cell that is already hidden or with font color set to white(not a good option as user can accidentally alter the value of that cell if it is not protected and if protected you need to unlock and lock the sheet every time you change the value in that cell). and change the counter every time you change the date value. and format the date cell with whatever color you want. you can also crerate an array for color codes where the counter will be the index number. So only thing you need to chech is if the index number is smaller than the counter and than change the cell color.