Help optimising my code

Hi all,

I have a macro-enabled workbook which has a user interface panel (main spreadsheet) and what can essentially be called a data bank (timetable spreadsheet). This is for my science department in our school. I designed it so that there are 4 macros that help us quickly and efficiently put pupils into detention, as well as see which pupils are due for detention on what days. It also lets us see which teacher (by referencing the teaching code) has the next supervision, as well as what dates they are due to supervise over the course of the whole year.

The only 4 macros in the main spreadsheet are as follows:

1. Add_pupil
2. Mark_pupil_present
3. Remove_pupil
4. Reset_date

That last one is very simple and only sets the date in cell E3 to today's date, should the supervising teacher quickly need to check who is in detention on that particular day.

So let me briefly explain how my macros function to (hopefully) help save you guys some time in answering my question:

Add_pupil - If pupil name (cell H3) is blank, gives a warning, otherwise goes along every column value in the 4th row on the timetable spreadsheet and compares the date in cell E3. Once the date matches, it then scrolls down that column and finds the next blank cell, and inserts a string composed of the teacher code (cell B2) & pupil name (cell H3). Once done, it then ends the macro (to prevent repeats).

Mark_pupil_present - The selected cell must fall between E7 and E26, otherwise an error message is thrown. Also, if a cell within this range is selected but there is no pupil present (i.e. there is just a "-" present) then it gives another appropriate warning. Otherwise, similarly as above, it goes along all the columns in the timetable spreadsheet, checking the value in the 4th row, until the date matches the date in cell E3. Then it scrolls through the column, finding the cell that has the same value as the selected cell (basically string matching) and then proceeds to append a string saying "PRESENT". Once done, it then ends the macro (to prevent repeats).

This then changes the cell to a green colour (conditional formatting, NOT part of the macro).

Remove_pupil - Identical search pattern as in Mark_pupil_present, but instead of appending a string, it simply clears the cell contents.

I was wondering if you guys could help me optimise my code; I'm by no means a VBA guru and the majority of what I know I've only learnt by copying macros from other forums (and then learning how it works by testing it out and modifying the code), so there could be a wealth of functions I am not aware of that probably do what I want even faster. I have heard of a search function that exists in VBA although I haven't read into it enough as of yet.

Many thanks for your help in advance and I look forward to seeing how I can improve my workbook!