Staff roster redesign - Vba code assistance

[Rancidveg]

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

 

[Craggs82]

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]

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