Staff roster redesign - Vba code assistance

Rancidveg

New Member
Joined
Apr 14, 2015
Messages
10
I have a spreadsheet which is used to produce a printable staff roster . It uses vlookup to place most of the information .
main issues are:
  1. constant duplication of names
  2. constant errors i.e rostering people when on leave
  3. takes an age for someone to compile
Its been three years and its never been correct not once!
Not wanting to reinvent the look , I thought that some of the errors could be eliminated with some error checking code
I was thinking of chopping it into tasks

Task one
Command button check for duplicates in cells and highlight for each day ( colour red) . if ok then msg "no errors

Task two
command Button Check names against a seperate list of staff who are on leave and highlight these names ( colour blue) .Staff could be ,not rostered or sick or on leave

The thinking behind each day having a separate command button to check is because the staff member is often pulled away or interrupted
Ï didnt want to go to a form , thought that was too complicated and trying to think through logically and modify what is already there

Having a hard time finding code to find and highlight cells in a column. Thought that would be easy not so

Is this realistic ? Any guidance , constructive comments would be appreciated


cut downduplicates.jpg
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Craggs82

Board Regular
Joined
Jun 28, 2016
Messages
56
Have you considered conditional formatting? that would give you a dynamic highlight as soon as the data is input and both of these tasks are entirely possible using that method.
 

mmldogs

New Member
Joined
Jul 31, 2019
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hadn't thought to do that . I would ideally like to have a list of names that shows if they are available to work or not and form there do conditional formatting to fill in the roster sheet
At the moment its a cut and paste then review .I would like to add as much error checking as I can and reduce duplicates
has there been or does anyone know of a staff scheduling roster template with conditional formatting ... links for further reading would be great so that I can adapt what i have in place
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top