Coloring the cells according to time

**User3** · 05-19-2011, 07:26 AM

I have a Time Table sheet which contains week days (in columns H to M), class start timing and end timing (in column N & O in attached sheet) and room number (in column P).
In the same file I have 6 other sheets which are named with week days i-e Mon, Tue onward.
In these 6 sheets I have time in column B starting from 7:00 AM and increasing by 5 minutes downward in the column till 10:00 PM, and room numbers in row 4.

A code is designed to pick three variable items i-e days, start and end time and room number from the main Time Table and highlight (with green colour) respective area in the sheets named Mon, Tue onward.

Example:
If time table contains a class which is held on Mon, Tue & Wed and starts at 9:00AM and end at 10:30AM and conducted in room number 1, code will pick this information and highlight the cells against timing from 9:00AM to 10:30AM under room number 1 in sheets Mon, Tue & Wed.

Code works fine but the only problem with it is that it highlights respective area considering 10 additional minutes. So in case of above example it highlights area from 9:00AM to 10:40AM rather till 10:30AM.

Note: Red coloured cells show that more than one classes are planned in one room in time which is coloured red.

I hope that I have explained the problem to ease the understanding of Excel experts and anticipating for a solution.

**watersev** · 05-19-2011, 02:03 PM

hi, User3, I will not correct existing code as I do not like algorithm it exploits, I can suggest my code to get it done. As far as I understand it works as it should. Please check attachment, run code "test". If there are any issues, please shout.