Hi all,
Thanks in advance for any help. I've been working on a ward rota excel project to reduce repetitive administrative tasks in my department. So far it's worked well, as you can see there is functionality to highlight staff in red on the main rota when they're in the annual leave rota underneath. I've attached a picture of my code/example in the sheet from our in house document to help people use the sheet. E4 is the start of the main rota, E87 is the start of the annual leave section.
The roadblock with this sheet comes down to when people copy and paste (despite me asking them not to and there being a message box on opening asking not to), because it copies conditional formatting and stops it from working well - as seen in above image.
A colleague's friend was able to mitigate this by using one match conditional formatting applied to all columns as opposed to my 10 which was brilliant, and I learned a lot. However, we then had to add a notes section underneath each day, see below pic, to detail when someone is starting at say, 11am. This then broke the single rule he set up and required the columns to have 10 different conditional formatting rules due to the merged cells. We have looked at cell comments for this but we need the information available on paper, and when it's printed the notes section is the only way to do this.
An old colleague recommended a macro to clear all conditional formatting and place it back in correctly, which is easy enough but this is dependent on others not inserting rows - and we may need to be able to do this as it's hard to define a maximum number of rows needed. I brainstormed that I could have a section where people fill in the row numbers present in the spreadsheet when they need to fix it, then a macro would use these values to select the ranges and input the correct conditional formatting that way - however I don't have the VBA skills yet to do this.
I wanted to throw this out there and see if anyone has any super efficient methods that I've completely missed, can I optimise the conditional formatting? Can I remove everyone's "ctrl" and "v" keys in the department so they can't paste?!
Thanks,
Matt (Excel noob!)
Thanks in advance for any help. I've been working on a ward rota excel project to reduce repetitive administrative tasks in my department. So far it's worked well, as you can see there is functionality to highlight staff in red on the main rota when they're in the annual leave rota underneath. I've attached a picture of my code/example in the sheet from our in house document to help people use the sheet. E4 is the start of the main rota, E87 is the start of the annual leave section.
The roadblock with this sheet comes down to when people copy and paste (despite me asking them not to and there being a message box on opening asking not to), because it copies conditional formatting and stops it from working well - as seen in above image.
A colleague's friend was able to mitigate this by using one match conditional formatting applied to all columns as opposed to my 10 which was brilliant, and I learned a lot. However, we then had to add a notes section underneath each day, see below pic, to detail when someone is starting at say, 11am. This then broke the single rule he set up and required the columns to have 10 different conditional formatting rules due to the merged cells. We have looked at cell comments for this but we need the information available on paper, and when it's printed the notes section is the only way to do this.
An old colleague recommended a macro to clear all conditional formatting and place it back in correctly, which is easy enough but this is dependent on others not inserting rows - and we may need to be able to do this as it's hard to define a maximum number of rows needed. I brainstormed that I could have a section where people fill in the row numbers present in the spreadsheet when they need to fix it, then a macro would use these values to select the ranges and input the correct conditional formatting that way - however I don't have the VBA skills yet to do this.
I wanted to throw this out there and see if anyone has any super efficient methods that I've completely missed, can I optimise the conditional formatting? Can I remove everyone's "ctrl" and "v" keys in the department so they can't paste?!
Thanks,
Matt (Excel noob!)
