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:
1. Unlike Excel 2003, the improved Conditional formatting can cotniue to evaluate the conditions if we turn off the setting located next to the conditions.
2. If the conditions continue to be evaluated, it will overwrite the format that meet the previous conditions, if they are in the same format, e.g. both conditions are set to fill up the cell with colours. Based on the above example shown in the diagram above, the cell will overwrite the red cell and turn yellow if it is found that the fifth condition is also true after the first condition turns true.
3. But if the format for the condition is not in the same category as the earlier condition, it will add on to the format. Based on the same example shown in the diagram above, the first condition will turn the cell into red if the condition is met. When the second condition turns true. The cell will continue to be coloured red. At the same time, it will draw a border for the cell.
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.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft