Forum Discussion
I want conditional formatting to update/refresh automatically
I keep a daily menu for my family on an excel spreadsheet that's open on my laptop at all times. I set up conditional formatting to highlight the current day's row of dishes and special notes. However, it does not automatically move to the next day's row unless I close the file and then reopen the file.
How do I get it to automatically highlight the next day at the stroke of midnight or else click something to get it to refresh when I look at the page?
You are probably using TODAY() to get the current date. TODAY() is a volatile function. It means that it recalculates whenever the spreadsheet recalculates. Reopening the file triggers the recalculation so the TODAY() function and conditional formatting updates.
You could also use a keyboard shortcut for manual recalculation - F9 or Fn + F9.
If you want it to be done 100% automatically then it is possible with help of macros.
I was not sure how your layout looks like, so I just drafted my version and added a button that lets you recalculate the whole workbook with a click. See the file attached.
