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

 

[Anbuselvam]

Try the below code and reply with a sample file link if you need further details.

Sub SearchTime()

Dim SearchTime As Date
Dim LastRow As Long
Dim MatchRow As Long

' Get the timestamp in cell A2
SearchTime = Range("A2").Value

' Find the last row of data in column C
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

' Loop through each cell in column C and find the closest match
MatchRow = 0
For i = 4 To LastRow
If Abs(Range("C" & i) - SearchTime) < Abs(Range("C" & MatchRow) - SearchTime) Then
MatchRow = i
End If
Next i

' Scroll to the matched row and show the previous 3 rows
Range("C" & MatchRow - 3 & ":C" & MatchRow).Select
ActiveWindow.ScrollRow = MatchRow - 2

End Sub

 

[palaeontology]

Thankyou, so much, for your reply and code.

Between posting the query and your response, I had added an extra column on the far left, so in your code, I had to change any reference to column A over to column B, and any reference to column C over to column D.

Sub SearchTime()

Dim SearchTime As Date
Dim LastRow As Long
Dim MatchRow As Long

' Get the timestamp in cell B2
SearchTime = Range("D2").Value

' Find the last row of data in column D
LastRow = Cells(Rows.Count, 4).End(xlUp).Row

' Loop through each cell in column D and find the closest match
MatchRow = 0
For i = 4 To LastRow
If Abs(Range("D" & i) - SearchTime) < Abs(Range("D" & MatchRow) - SearchTime) Then
MatchRow = i
End If
Next i

' Scroll to the matched row and show the previous 3 rows
Range("D" & MatchRow - 3 & ":D" & MatchRow).Select
ActiveWindow.ScrollRow = MatchRow - 2

End Sub


I then pasted the edited code into a module (module 2), saved the file, and ran the program but unfortunately, it hasn't had the effect I was hoping for.

I suspect your code is correct, and that I've messed something up, or missed something.

You mentioned I should reply with a sample file link if I had problems, but I've never done that before and not sure how.

I pressed the 'Insert Link' option from the top ribbon, but it's asking for a URL.

Can you please let me know how to provide you with a link to my file ?

Again thankyou so much for putting that code together.

I'm at the stage where I can read and understand what code is saying, but not at the stage of being able to write code myself.

Kindest regards,

Chris

 

[Anbuselvam]

Sub SearchTime()

Dim SearchTime As Date
Dim LastRow As Long
Dim MatchRow As Long

' Get the timestamp in cell B2
SearchTime = Range("B2").Value

' Find the last row of data in column D
LastRow = Cells(Rows.Count, 4).End(xlUp).Row

' Loop through each cell in column D and find the closest match
MatchRow = 0
For i = 4 To LastRow
If Abs(Range("D" & i) - SearchTime) < Abs(Range("D" & MatchRow) - SearchTime) Then
MatchRow = i
End If
Next i

' Scroll to the matched row and show the previous 3 rows
Range("D" & MatchRow - 3 & ":D" & MatchRow).Select
ActiveWindow.ScrollRow = MatchRow - 2

End Sub

 

[palaeontology]

Ta again for such a speedy response.

It's still not working and comes up with the following message ...

which when I press 'debug' gives this message ...

I must be doing something wrong at my end

 

[Anbuselvam]

The error message "Method range of object global failed" typically indicates that the code is unable to access a particular range of cells, possibly due to an incorrect reference or an issue with the Excel application itself.

In this code, the error may be caused by one of the following lines:

• SearchTime = Range("B2").Value
• If Abs(Range("D" & i) - SearchTime) < Abs(Range("D" & MatchRow) - SearchTime) Then
• Range("D" & MatchRow - 3 & ":D" & MatchRow).Select

To address this issue, try adding a reference to the specific worksheet that contains the cells being accessed. For example, replace "Range" with "Worksheets("Sheet1").Range" to specify that the range is on "Sheet1". Alternatively, make sure that the active worksheet is the one containing the cells being accessed before running the code.

Also, make sure that the workbook and worksheet names are spelled correctly, as even a small typo can cause the code to fail.

 

[palaeontology]

ok, if the code is getting hung up on the time in B2, can we reference the time as a value, rather than as a time ?
If so, I've restructured the spreadsheet a little to accomodate the relevant times as values ...

So, I tried to alter your code to compare the possible Race Clock Time 'values' in column D with the actual Current Clock Time 'value' in cell C2, but the same line is being highlighted with the same error

So does that suggest it's not having a problem with referencing the time in B2 or even C2 ?

If so, then as you said, is the issue in the yellow code line ?

I'm not understanding your use of MatchRow = 0

I know we're close to solving this, but I've tried a few alterations, and none seem to have worked.

Any ideas ?

 

[Anbuselvam]

I think The value > 0.655960648 is not found in Column "D"

MatchRow =0 is the starting point value and then it will assign D4 till the last row.

Make sure C2 and D4:LastRow must be in the same cell format.

Don't post screenshot images as it is hard to transfer for checking. post the code fully as well as use XL2BB

 

[palaeontology]

For the screenshot provided, the value (in column D) became greater than C2 down in row 50, and so all column D values below that are also greater than C2

is it possible that the code gets hung up when the line ... If Abs(Range("D" & i) - SearchTime) < Abs(Range("D" & MatchRow) - SearchTime) replaces MatchRow with 0 to read as cell D0 ?
Because I would imagine D0 is a cell that doesn't exist.

C2 and D4:LastRow are all Number-formatted as 'general'

Ok, will definitely post the code fully from now on ... my apologies.

Kind regards,

Chris

 

[Anbuselvam]

I just entered the whole number and tried out the code. it is working on my end. need to check the format of the search value and D column values from your end.

Sub SearchTime()
    Dim SearchTime As Double
    Dim LastRow As Long
    Dim MatchRow As Long
    SearchTime = Worksheets("Sheet1").Range("C2").Value
    LastRow = Worksheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
    MatchRow = 0
    For i = 4 To LastRow
        If SearchTime >= Range("D" & i).Value Then
            MatchRow = i
        End If
        Next i
'        MsgBox MatchRow, vbOKOnly
    Worksheets("Sheet1").Range("D" & MatchRow).Select
    ActiveWindow.ScrollRow = MatchRow - 2
End Sub