Hi all
I'm trying to create a template and a macro for Line of Balance. Line of balance is essentially like a visual recovery plan of when something will be finished and delivered.
Normally at our work we create these LOB-s on spreadsheets manually. We have all the necessary dates of each process and we colour certain cells in certain colours. This colouring part is very frustrating task. Especially if dates are constantly changing then you have to constantly rework them. Mistakes can be very easily made. So I would like to create a macro to make it semi automatic. So after you change the dates you will run macro to refresh and it would colour the cells accordingly.
In cells A2 to B9 I have the colour codes and cells higlighted by certaing colour
A2 to A9 are the colour codes like: b, r, a, g, p, y, lb, gr
B2 to B9 are the cells that have been highlighted in following colours: blue, red, amber, green, purple, yellow, light blue, gray. So it looks like this (note I use words to describe the colour of the cell from B2 to B9):
-------------------- Col A ---------------------- Col B--
Row 1----------- Colour code ------------ Colour -----------
Row 2----------- b --------------------------- blue ----------------
Row 3----------- r ---------------------------- red -----------------
Row 4----------- a --------------------------- amber ------------
Row 5----------- g --------------------------- green --------------
Row 6----------- p --------------------------- purple -------------
Row 7----------- y --------------------------- yellow -------------
Row 8----------- lb -------------------------- light blue ---------
Row 9----------- gr ------------------------- gray ----------------
On row 12 I have dates from that cover essentially 3 months period (horizontally from M12 to FW12, e.g. from 12/09/2011 to 25/02/2012). I have used date format dd so visually it will not show the full date in dd/mm/yyyy format, instead it will show the days e.g. 12,13,14,15 etc.
Now I have some information in vertically in columns C, J, K, and L. Information in Col C will not be involved in terms of the macro but it will be easier to explain.
Col C = Process
Col J = Start date
Col K = Finish date
Col L = Colouring code
Example is below:
--------------------- Col C --------------------- Col J ---------------------- Col K -------------------- Col L -------
Row 12----------- Process ---------------- Start ---------------------- Finish ------------------- CC ----------
Row 13----------- Process 1 ------------- 12/09/2011 ------------- 16/09/2011 ------------- y -------------
Row 14----------- Process 2 ------------- 12/09/2011 ------------- 20/09/2011 ------------- y -------------
Row 15----------- Process 3 ------------- 21/09/2011 ------------- 22/09/2011 ------------- y -------------
Row 16----------- Process 4 ------------- 23/09/2011 ------------- 23/09/2011 ------------- y -------------
Row 17----------- Process 5 ------------- 12/09/2011 ------------- 16/09/2011 ------------- r -------------
Row 18----------- Process 6 ------------- 17/09/2011 ------------- 22/09/2011 ------------- p -------------
Row 19----------- Process 7 ------------- 23/09/2011 ------------- 30/09/2011 ------------- p -------------
Row 20----------- Process 8 ------------- 01/10/2011 ------------- 02/10/2011 ------------- p -------------
Ok, first I would like the macro to clear all highlights so that means any coloured cells from M13 (inclusive) to the the bottom and far right of the spreadsheet.
Now the macro should do the following:
check the column L for the colour code (lets say there is letter "y" in L13), then check the area A2 to A9 to match colour code with colour (y = A7, matches the colour index from B7, e.g. ColorIndex = 6), now it checks column J for start date and column K for the finish date, it then matches the date range as per row 12 (M12 to the right), and then highlights the respective cells (M13 to P13, e.g. from 12/09/2011 to 16/09/2011).
Macro would leave all rows untouched that do not have any colour code (as per column L).
I have attached a spreadsheet with sheets "before" and "after" to give a better understanding what I'm after. I'm sure other people could also find use for this kind of macro.
PS! I apologize if my English is not correct as its not my native language.
Any help is most welcome.
Cheers
Bookmarks