Jul 09, 2015

When you design an automated calendar in Microsoft Excel, you don’t need to colour the weekends yourself. With the conditional formatting tool, you can automatically change the colours of weekends by basing the format on the WEEKDAY function. Assume that you have the date table–a calendar without conditional formatting:

Before:

After

To change the colour of the weekends, select only cells B1 and B2 to make this an interactive month and year example, open the menu Conditional Formatting > New Rule.

Select the menu Use a formula to determine which cell to format.

In the text box format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2)>5

(The parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends. Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.)

Then, customise the format of your condition by clicking on the Format button and choose a fill colour (green in this example).

Click OK, then open Conditional Formatting> Manage Rules

Select This Worksheet to see the worksheet rules instead of the default selection. In Applies to, change the range that corresponds to your entire table when creating your rules to extend it to the whole column. Now you will see a different colour for the weekends.