Hi folks
I have two issues with my spreadsheet so to start i'll explain what I want to happen.
I have a workbook which records what type of work i'm doing and changes the appropriate rows to a certain colour depending on what type of work i'm entering onto the spreadsheet (i've asked about that here before, that works fine).
I have attached a copy of the spreadsheet, but on the left hand side is the log of what work i'm doing, and on the right is an automated log of my performance against targets. There are several types of work; assessments, tasks etc. Each type has a colour and when you press the appropriate letter in the Type field (a for assessments, for example), the whole corresponding row turns green, as you will see on the spreadsheet.
What I want to happen is to be able to change the colours of the box on the right, for example changing the green assessments box to yellow, and for that colour to update any entried already entered onto the spreadsheet. All I've been able to manage so far is to get it working so that if I change the colour of, let's say the green assessments box on the right, all future assessments entered on the left will update with the new colour, but the already entered ones will stay green.
So far I have the following code to update the cells as I want to:
Please note that the variable 'assessmentnumber' is mentioned earlier in the code and points to Cell G6, which contains the colour code of the assessment colour box, in this case green.
The code above is located in the Worksheet_change sub and currently alters the colour of the first assessment recorded (in E5) once I've changed the colour in the assessment colour box (H6) and then clicking on a different cell.
In theory I could just repeat the code above for every cell from H5 to H100 and for each letter (a, e, i, o, t and s are all used) but there's surely an easier way than this. In essence I need a way to search every cell in the range E5 to E100 for what (if any) letter is in the E column and then change the colour of the relevant row depending on what colour is in the corresponding H column.
Another issue which is linked to this is that I want the change to be automatic and not rely on me switching to a different cell before the change takes effect. You will see on the spreadsheet code that the G cells record the colours of the appropriate H cells, and that this is performed through the selection_change sub. I have attempted to just move this code to the calculate sub, however the code generates stack errors and often just crashes excel.
I hope someone can help me with this, I realise I may not have explained myself very well so let me know if you want me to provide more detail.
workV2colourtest.xls
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks