Solved Formatting cells for schedule If...

Microsoft Office 2007 home and student
August 29, 2011 at 13:15:05
Specs: Windows Vista
First: Explaining the workbook:
I have a workbook i have created to keep track of my schedule. It contains two sheets.

SHEET ONE (Schedule) is the schedule template for a week. In cell D1 is where I enter the first day of the week. Header row is C3 to I 4 (this displays current weekday and date of each) I have created formulas for each cell to determine the weekday and date (d). In cell A5 (which I merged with A6), I have the number 8. In cell B5 I have "am" in cell B6 I have ":30". This helps to show the schedule in half hour increments across the sheet. I have done similarly until 10pm.

SHEET TWO (Appts) has a table set up in cells B38:B708. Row 38 is the header row. Column titles are B38="Recurring Day", C38="Fixed Date", D38="Start Time", E38="End Time", F38="Appointment". Each cell below the title in column B is formatted with a list to pick a weekday (Monday to Sunday).

The purpose of sheet two is to enter appointments (or classes, etc). For example, every Monday from 3:30pm until 9:30pm I have a lecture. If there is a irregular appt. such as a test on September 1st, I would put the date 01/09/2011 in the Fixed Date column instead of selecting a weekday, then put in the start and end times and apt. details. I always want irregular appointments to take precedence.

I'd love it if cells would merge on the 1st sheet based on the schedule (to show a block of time), however, I do not see how that would be possible. So...

MY QUESTION: Is it possible to create a formula (or conditional formatting) that would colour the required cells on sheet one to make them appear as merged and only display the "Appt" in the first of those cells?

I don't know where to begin looking for an answer for this. I've been looking online but have had no success. I may not have the wording right, or maybe this just isn't possible. Any help or direction would be appreciate. If there is somewhere to send the workbook, I'd be happy to do so.


See More: Formatting cells for schedule If...

Report •

August 30, 2011 at 12:27:41
✔ Best Answer
I have not put together a spreadsheet based on your exact description to try this, but here's a concept:

If you have a header row with "times" in half hour increments, could you use Conditional Formatting to see if the times in that header row are between the Start and End times of your appointment?

I set up quick test with this:

     A     B     C     D     E     F     G
1               7:00  7:30  8:00  8:30  9:00
2  Start  End
3  7:30   8:30				

I then selected C3:G3 (all at once) and used this formula for Conditional Formatting, with afill color of Red:


In my example, D3:F3 turned Red

As I alter the Start and End times between 7 & 9, the Red fill color follows. 7:00 & 9:00 would fill them all.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 13, 2011 at 10:24:19
Thank you. This has helped to send me in the right direction! :-)

Report •

Related Solutions

Ask Question