Enhancing my original excel calendar has provided me with the opportunity to test the improved conditional formatting made available in Excel 2007 and 2010 to the limit. The original calendar was able to highlight the dates with different colours for public holidays, school holidays and special activities, which in my case, are my excel courses. It has served me very well for the last few years. But as our room rental activities picked up, I realized that it is not able to serve our needs anymore. The original calendar can only provide a single shade to identify whether the room is used for that day. It cannot differentiate whether it is used in the day, evening or both. So I decided to enhance it with the improved conditional formatting function in Excel. What I thought was a simple task turned out to be very challenging as I grapple with how the conditional formats behaves under the different conditions. For example, the cell will be shaded diagonally from the left to the right when the room is used in the day. When the room is also used in the evening, my original idea was to have the cell shaded diagonally from the right to the left. In this way, the cell will be crossed with lines if the room is used both in the day and evening. But it did not behave that way. The shade will over-write the earlier one. So in the end, I have to figure out a way to differentiate the usage for the day and the evening. In the process, I get to understand how the improved conditional formatting works:
Other than conditional formatting, I have also changed the way dates are input into the calendar. In the original excel calendar, the cell will change to the designated colour when the dates are entered in a certain range.
But in the upgrade calendar, there is no need to do so. I have designated a central location where the dates can be entered. To change the cell colour, just put the same code in colour column AF and the extreme right cell within the header. The dates will automatically match the course name and show up in the specific range and at the same time, turns the date into the colour displayed by the header. This is done with a complex array formula which acts like an auto-filter function, listing only entries that meet the criteria.