A simple way
Select the range with dates on the sheet and select on the tab Home – Conditional Formatting – Cell Selection Rules – Date (Home – Conditional Formatting – Highlight Cell Rules – Date Occuring). In the window that opens, select the desired lighting option from the drop-down list:
Difficult but beautiful way
Now let’s analyze the problem more difficult and more interesting. Suppose we have a large supply table of some goods:
Please note the shipping date. If it is in the past, then the goods have already been delivered – you don’t have to worry. If it is in the future, then we must keep the issue under control and do not forget to organize the delivery by the specified date. And finally, if the date of shipment coincides with today, then you need to drop everything and deal with this particular batch at the moment (highest priority).
For clarity, you can set up three conditional formatting rules to automatically fill the entire line with batch data in different colors depending on the date of shipment. To do this, select the entire table (without a header) and select on the tab Home – Conditional Formatting – Create Rule (Home – Conditional Formatting – Create Rule). In the window that opens, set the last rule type Use a formula to determine which cells to format (Use formula to determine which cell to format) and enter the following formula in the field:
This formula takes the contents of cells E5, E6, E7… in sequence from the ship date column and compares that date with today’s date in cell C2. If the shipment date is earlier than today, then the shipment has already taken place. Notice the dollar signs used to anchor links. The reference to $C$2 must be absolute – with two dollar signs. The reference to the first cell of the column with the date of shipment should be with fixing only the column, but not the row, i.e. $E5.
After entering the formula, you can set the fill and font color by clicking the button Framework (Format) and then apply our rule by clicking on the button OK. Then repeat the whole procedure to check future deliveries and deliveries for the current day. For shipped batches, for example, you can choose gray, for future orders – green, and for today’s – urgent red:
Instead of the current date, you can insert the function into cell C2 TODAY (TODAY), which will update the date each time the file is opened, which will automatically update the colors in the table.
If such illumination is not always needed, but only for a certain time of working with the table, then you can add a kind of switch to what has already been done. To do this, open the tab developer (Developer). If it is not visible, then first turn it on through File – Options – Customize Ribbon And click the Insert (Insert):
In the list of tools that opens, select Checkbox (Checkbox) from the top set Form controls and click on the place on the sheet where you want to place it. Then you can set the size of the inscription and change its text (right-click – Change text):
Now, in order to use the checkbox to turn the highlight on or off, you need to link it to any cell on the sheet. Right-click on the drawn checkbox and select the command from the context menu Object Format (Format Object) and then in the window that opens, set any suitable cell in the field Cell communication (Cell Link):
Check how everything works. Linked cell E2 should output TRUE when the checkbox is enabled, or FALSE when it is disabled.
Now it remains to add one rule to the conditional formatting so that our checkbox turns the date highlighting on and off. Select our entire table (except for the header) and open it in the tab Home — Conditional Formatting — Manage Rules (Home — Conditional Formatting — Manage Rules). In the window that opens, the rules we created earlier for highlighting past, future and present dates in different colors should be clearly visible:
Press the button Create Rule (New Rule), select the last rule type Use a formula to determine which cells to format (Use formula to determine which cell to format) and enter the following formula in the field:
We do not set the format and click OK. The created rule should be added to the general list. Now you need to raise it to the first line with the arrows (if it is not already there) and turn on the checkbox opposite it on the right Stop if true (Stop If True):
Parameter with obscure name Stop if true does a simple thing: if the rule against which it stands is true (i.e. our flag Timeline highlighting on the sheet is turned off), then Microsoft Excel stops further processing of the rules, i.e. does not advance to the next rules in the conditional formatting list and does not flood the table. Which is what is required.
- Conditional formatting in Excel 2007-2013 (video)
- Zebra striped table rows
- How Excel actually works with dates and times