Excel/VBA help

[CU Tiger]

I need some help modifying/reformatting existing VBA code on an excel spreadsheet.

Currently we use this spreadsheet as a time keeper for some local racing events I am involved with.

The basic mechanical function is as a competitor completes a lap, a time keeper types their id number and it timestamps right then to record the lap completion.

I developed this about 2 years ago with a friend I no longer have contact with, and after using it a while we would like to change the way some things operate and look.

2. I'd like to add in a column for start time (hand typed depending on class, start order, etc.) just beside the Lap 1 column.

3. As you may can see, the spreadsheet allows us to type in the vehicle number and hit enter and the timestamp is entered into "this time" cell for that appropriate car number. The next time it moves the timestamps over to "prev time" and into the Lap 1 cell. But, it is entering the lap time in the Lap 1 cell when we enter the vehicle number for the second lap. It records all the correct lap times, it just doesn't post to the Lap 1, 2, 3, etc. chart on the right right away. The lap time posting is 1 lap behind. I'd like to enter the lap time one data entry cycle sooner. Id liek to eliminate the this time previous time cells and have the value go direct to the next open lap cell.

Essentially, I'd like to have a column just to the left of the Lap 1 column that shows each vehicle's start time, which we manually enter. When we say "go", they start and we don't enter anything. As they pass the start/finsh line for the first time (lap 1 completion), we enter their vehicle number and press enter. I'd like this timestamp to immediately populate the Lap 1 cell for that vehicle. It will do that now, but it only populates it as they come in for lap 2. We want to use this spreadsheet for public viewing, so it will confuse the public as well as some of the organizers if we leave it as is.

4. We'd also like the time in the Lap 1, 2, 3, etc. cells to either equal lap time or actual time and have another cell which calculates that lap time. Right now it only posts lap time, I think.

5. At the end of the lap columns, I'd like to have a Total Time that will add up all of the time posted for each lap for each vehicle number. Races typically run for 2 hours. I would like to have the cells where timestamps are entered turn red if the recorded time is more than 2 hours from the manually entered Start Time. Since times MAY be 2 hours for everyone or MAY change, can we have a cell at the top of the page where we enter the length of the race (2 hours, 1.5hours, 8 hours, etc) that will be the reference cell for changing the cells red for the time expired timestamps?

6. something that would be really nice to incorporate would be to have a live updated race positioin based on start time, laps, and elapsed time. Obviously it won't update until vehicles pass through the start/finish line. If there are 20 competitors, I'd like to have a column that lists who is currently in 1st down to last that changes as operate the spreadsheet during race day. # of laps completed would be the key function then total elapsed time, then corrected for the staggered starts that we manually enter

I am attaching a copy of the spreadsheet, and eternally grateful to anyone who can offer any assistance.

EDIT: Bah I cant attach the .xlsm file type but could email it to anyone who would take a look

[Suburban Rhythm]

I probably can not look at it today, at least during the day. But possibly over the weekend.


for #5, you can do this with conditional formatting.
As you suggested, you could have a cell designated as the "estimated time" (2 hours or whatever), then use conditional formatting to say if entered time causes the race time < the estimated time, highlight the cell, turn font red, etc.