VBA code to keep a particular row 4 rows below te 'freeze' line

[palaeontology]

I have the following spreadsheet that has frozen the top 3 rows.

I have some conditional formatting that highlights cells from column F through to column N when the now() time ... seen in cell A2 .... hits the time displayed in column C. Those cells will remain highlighted for just 5 seconds, and then the next row down will be highlighted for 5 seconds, etc etc etc..

Here is the conditional format rule ... =AND($C4<=$A$2,$C4>$A$2-TIME(0,0,5))

Is there some VBA code that will always keep the highlighted row 4 rows below the freeze line ?

For example, the image above shows the current time to be 9:18:34, so cell C97 satisfies the conditional format rule, therefore cells F97 to N97 are currently highlighted.

I need some code that would currently force row 97 to sit exactly 4 rows below the freeze line, then when row 98 becomes the highlighted row (due to satisfying the conditional format rule) it would become the row psoitioned exactly 4 rows below the freeze line.

Is this possible ?

Kind regards,

Chris

 

[palaeontology]

did what you did and checked whether cells in column D were larger than cell C$2 ... see below .... so it seems like it's reading cell C$2 fine, however, when I ran your adjusted code, I got another error message

 

[Anbuselvam]

Sheet 1 exists? or any other name you have given them? If yes change sheet 1.

 

[palaeontology]

Is it possible to go at it a different way ?

When a race begins, I use a command button (CommandButton11) on a userform (UserForm1) that, when pressed, throws a timestamp into cell B1.
Sidenote .... if B1 is empty, a timestamp is thrown into it. If there is an existing timestamp in B1, it can be over-written (only if the original timestamp was a mistake pressing of the command button).

Either way, there will be a change of event occuring in cell B1 to start the race.

Is it possible to code it to recognise when a timestamp is thrown into cell B1, and that after 15 seconds, the sheet scrolls one row every 5 seconds, and after 30 minutes, all automatic scrolling for that sheet ceases.

Sheet 1 records the race for the Year 7 Girls, and the other races (all the way up to Year 12 Boys) are all recorded on their own sheets in that same workbook.

So any instruction would have to be sheet-specific, and unaffected by any activity performed on the other sheets .... we can have up to 3 races on the course at one time, so there will be up to 3 sheets being worked on at any time.

 

[palaeontology]

Sheet 1 exists? or any other name you have given them? If yes change sheet 1.

In the image below you'll see what race belongs to which sheet ... for example, the Year 7 Girls (7G) race belongs to sheet 1, the Year 7 Boys (7B) race belongs to sheet 2, etc etc