munkifisht
New Member
- Joined
- Mar 17, 2005
- Messages
- 39
Ok, I am very unVBA savvy, well what I want to do is to change set the background colours and format of rows of cells based on the values of one of these cells. I have this working to some extent using conditional formatting where if the value of the cell is ‘Done’, ‘In Progress’ or ‘On Hold’ it changes the value of the row to a specific colour, the problem is I may have up to 10 different states for this.
Now, here is an example of one of my sheets:
____B____________|C________|D_____|E_______|F______|G___|H____|I______|J___|
8_|_Main_Task_No|_Task_No|Task__|Engineer|Status_|Done|To_Do|Planned|Over|
9_|_A1___________|_________|Task_M|________|To_Do__|____|_____|_______|____|
10|______________|A1.1_____|Task_1|_E.ng___|To_Do__|00__|04___|04_____|00__|
11|______________|_________|Totals|________|_______|Sum_|Sum__|Sum____|Sum_|
What I want the program to do is to see the state of cell f?? and then based on a key that I have created, I want the program to change the backgrounds of cells C?? to J?? based on this key, ie read the value, find the same value in the key, find the background colour of the cell next to that value, and apply that to all cells Bn-Jn where n is the value of the row. Also when the cell in B is not blank I want that to be filled as well and I also want the text to turn bold.
I was hoping also to have a list sorted on a separate page as well so you could select a value for column E and F from that list. From what I can see, excel only allows you to generate lists on the page you wish to display them, through validations that is. I also don’t want the main headings to be have this condition applied to them, ie the lists (rows 8, 9 and 10 are only chosen for convenience, in reality they may be any numbers) are only applied where they have to be, and before the totals are calculated. In all cases the sheets start the same way, ie with ‘main task no’ in cell B? and end the same with the totals, but other information is included before and after this.
Like I said I am pretty much a novice on VBA, so any help would be great.
Thanks.
Now, here is an example of one of my sheets:
____B____________|C________|D_____|E_______|F______|G___|H____|I______|J___|
8_|_Main_Task_No|_Task_No|Task__|Engineer|Status_|Done|To_Do|Planned|Over|
9_|_A1___________|_________|Task_M|________|To_Do__|____|_____|_______|____|
10|______________|A1.1_____|Task_1|_E.ng___|To_Do__|00__|04___|04_____|00__|
11|______________|_________|Totals|________|_______|Sum_|Sum__|Sum____|Sum_|
What I want the program to do is to see the state of cell f?? and then based on a key that I have created, I want the program to change the backgrounds of cells C?? to J?? based on this key, ie read the value, find the same value in the key, find the background colour of the cell next to that value, and apply that to all cells Bn-Jn where n is the value of the row. Also when the cell in B is not blank I want that to be filled as well and I also want the text to turn bold.
I was hoping also to have a list sorted on a separate page as well so you could select a value for column E and F from that list. From what I can see, excel only allows you to generate lists on the page you wish to display them, through validations that is. I also don’t want the main headings to be have this condition applied to them, ie the lists (rows 8, 9 and 10 are only chosen for convenience, in reality they may be any numbers) are only applied where they have to be, and before the totals are calculated. In all cases the sheets start the same way, ie with ‘main task no’ in cell B? and end the same with the totals, but other information is included before and after this.
Like I said I am pretty much a novice on VBA, so any help would be great.
Thanks.