Coding Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2614370%22%20slang%3D%22en-US%22%3ECoding%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2614370%22%20slang%3D%22en-US%22%3EHi%20everyone%2C%3CBR%20%2F%3EI%20have%20an%20annual%20leave%20tracker%20that%20I%20want%20staff%20to%20be%20able%20to%20highlight%20the%20days%20they%20want%20to%20take%20off%2C%20push%20a%20button%20and%20it%20changes%20the%20colour.%3CBR%20%2F%3EI%20also%20want%20it%20to%20then%20deduct%20the%20colour%20from%20their%20leave%20balances%20to%20keep%20a%20running%20total%20of%20what%20they%20have%20left.%20We%20have%20no%20HR%20system%20so%20everything%20is%20very%20manual.%20Any%20help%20would%20be%20greatly%20appreciated!!!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2614370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2614469%22%20slang%3D%22en-US%22%3ERe%3A%20Coding%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2614469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1121154%22%20target%3D%22_blank%22%3E%40AB0311%3C%2FA%3E%26nbsp%3BFar%20from%20perfect%2C%20but%20I'm%20attaching%20a%20schedule%20that%20might%20inspire%20you%20to%20create%20something%20for%20your%20organization.%20I%20haven't%20bothered%20to%20fill%20in%20the%20week-end%20or%20public%20holidays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20having%20people%20select%20cells%20and%20then%20push%20a%20button%2C%20why%20not%20teach%20them%20to%20enter%20a%201%20in%20the%20days%20that%20they%20have%20taken%20(or%20will%20take)%20leave.%20So%20much%20easier%20to%20calculate%20with%20numbers%20that%20with%20colored%20cells.%20No%20coding%20needed.%20The%20orange%20coloring%20was%20done%20with%20conditional%20formatting.%20It%20highlights%20all%20cells%20that%20are%20not%20blank.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi everyone,
I have an annual leave tracker that I want staff to be able to highlight the days they want to take off, push a button and it changes the colour.
I also want it to then deduct the colour from their leave balances to keep a running total of what they have left. We have no HR system so everything is very manual. Any help would be greatly appreciated!!!
3 Replies

@AB0311 Far from perfect, but I'm attaching a schedule that might inspire you to create something for your organization. I haven't bothered to fill in the week-end or public holidays.

 

Rather than having people select cells and then push a button, why not teach them to enter a 1 in the days that they have taken (or will take) leave. So much easier to calculate with numbers than with colored cells. No coding needed. The orange coloring was done with conditional formatting. It highlights all cells that are not blank.

Thank you! So every time they select 1 it changes the colour of the cell.
If they are booking 2 weeks off can they C&P or do they have to type 1 in every cell?

@AB0311 Alternatively, select all the cells for the days to be taken off, enter 1 and press Control-Enter. That will enter a number 1 in all selected cells in one go. And if you make a mistake, just delete the ones that are wrong. 

 

By the way, this method also allows people to take just half a day off. Just enter 0.5 in such case.