Conditional Formatting

Occasional Contributor


Does anyone know if it is possible to have the color fill of the cell change each time the number in the cell is changed? For example, I have a macro that changes the date in the cell every time the appointment is rescheduled, so the first time I use the macro in that cell I would want it to be green, and if the date changes again (i.e they reschedule again), I want the cell to then turn orange, and if the date changes again, then I would want it to be red. 

Is there a way to include this in the macro that I use to change the date in the cell?


3 Replies

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.


How would I add a counter to the cell in a hidden sheet so that it appears on the sheet that is visible? Sorry, new to excel and just a tad confusing at first. 


sorry I think I have written this comment to totally wrong entry.


You can use a conditional formatting in the first place. so whenever the cell changes Excel automatically formats the cell color.

If you want to include it in the macro so that whenever you change the cell value, InteriorColor property can be used.