Weekly Schedule: Merge & format cells + insert & format text based on input in other cells

[Marbleking]

Hi,

Edit: I have attached a Workbook containing examples of what I'm trying to achieve, including a VBA that does approximately what I want to do. Sheet2!K2:Q22 contains the ranges I want the VBA to merge in Sheet1. Sheet2!R2:R22 contains the text I want the VBA to add into the corresponding merged ranges in Sheet1, and Sheet2!S2:R22 contains the formatting I want the VBA to add to the merged cells in Sheet1.

Original Post:

I am creating a weekly schedule and need help with a VBA to perform merging and formatting of cells:

The weekly schedule is a table in Sheet1 with 49 rows for each half hour of a day (A2:A50) from 00:00 hours to 24:00 hours and 7 columns for each day of the week (B1:H1).

In Sheet2 I have a list of weekly events in Column A. For each of the events in Column A, the corresponding rows in Columns B to H in Sheet2 list the corresponding ranges in Sheet1 that shall be merged for each of the 7 days. These may be continuous (connected across days) or noncontinuous ranges (events that appear at several separate times of the week).

For example, if an event Sheet2!A2 starts Monday at 19:00 PM, continues through 24:00 PM and and ends Tuesday at 08:00 AM, Sheet2!B2 would show Sheet1!$B$40:$B$50 and Sheet2!C2 would show Sheet1!$C$2:$C$18 to indicate which cells in Sheet1 that should be merged. (Sheet2!D2:H2 would be blank, since these cells indicate cells to be merged for Wednesday through Sunday, but event Sheet2!A2 in this example only occurs Monday and Tuesday).

Also, cells in Column I of Sheet2 contain text that shall be copied into the merged cells in Sheet1 for each corresponding event. In the example above, that would be text in Sheet2!I2 copied into the merged cells Sheet1!$C$2:$C$18 - the BIGGEST of the merged areas for the event in Sheet2!A2 (to make sure the text fits properly into the most spacious of the merged areas for the event).

Lastly, cells in Column J of Sheet2 contain the desired formatting to be applied in the merged cells in Sheet1. In the example, cell Sheet2!J2 would be formatted to Arial Bold 10 in black with red background colour and center alignment. This formatting should be applied to the merged cells for event Sheet2!A2 in Sheet1.

Each of the events listed in Column A of Sheet2 have their own set of cells to be merged, texts to be inserted and formats to be applied - and all of it must be (re-)applied to the weekly schedule at the click of a control button.

I'm at odds with how to pull this off in VBA and would appreciate some expert suggestions.