Merge and Center Code

[Chris Michaels]

Hi,

I am creating a model that does forecasting and planning for a company. I have a table with all the months in different columns and directly above it says either Actual or forecasting. I have a drop down list and I was hoping to use the drop down list to tell what cells to merge together and color as actuals and which to move together and color as forecasting.

For example if the drop down list is august then all the cells above January through August (D6:K6) would merge together and display "Actuals" in a dark blue formatting color and September through December cells (L6:O6) would merge together and display "Forecast" in a light blue formatting color. And vice versa if the drop down only displayed February then only January and February (D6:E6) would be merged together and display "Actuals" and March through December (F6:O6) would merge together and display Forecast.

I attached a picture of the model. Any help would be extremely appreciated.

Thank you!!

[JBeaucaire]

I think you uploaded the wrong document. Instead of a sample Excel file we could use to quickly demonstrate an answer, you uploaded a Word doc with a picture.

You can use MATCH() to determine where on that row the word August appears.
=MATCH(F3, A5:L5, 0)

Then you can use Conditional Formatting across A5:L5 to change the color characteristics based on if the column number is less than, equal to, or greater than the Match.

[Chris Michaels]

Is there a code that I can put down

If cell E1=x merge E6 and D6 and write "actuals" and if F1=x merge F6 through D6 & write "actuals", if G1=x merge G1 through D6 & write "actuals" and so forth?

[JBeaucaire]

I could probably rig up a macro with a workbook to use. Got one handy?

Something that triggers itself when you change the month?

[Chris Michaels]

I attached the example model. Thank you for your help!!

[JBeaucaire]

Right-click the tab and select VIEW CODE, then paste in this event code that watches for changes to cell F4.

Please Login or Register  to view this content.

[Chris Michaels]

I cannot thank you enough this is amazing!!

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.