Change range in worksheet_change event code every Jan 1?

[Ironman]

I've just been very kindly given the below code by Eric W relating to this post

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As Variant, OldMax As Double

    If Not Intersect(Target, Range("C8413:C8777")) Is Nothing Then
        MyData = Range("C8413:C8777").Value
        MyData(Target.Row - 8413 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum distance achieved"
    End If
    
    If Not Intersect(Target, Range("D8413:D8777")) Is Nothing Then
        MyData = Range("D8413:D8779").Value
        MyData(Target.Row - 8413 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum time achieved"
    End If
    
End Sub

I'd be grateful, if it's possible, for the above ranges to be changed automatically each year on Jan 1 by the number of days in the year (i.e. taking account of leap years)

e.g. On 1 Jan 2022 both ranges will change from C8413:C8777 and D8413:D8777 to C8778:C9142 and D8778:D9142

Many thanks!

 

[Peter_SSs]

Untested, but I think this should do it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyData As Variant, OldMax As Double
    Dim ThisYearRow1 As Long, ThisYearDays As Long
    Dim rng As Range
    
    Const BaseRow As Long = 8413
    
    ThisYearRow1 = DateSerial(Year(Date), 1, 1) - DateSerial(2021, 1, 1) + BaseRow
    ThisYearDays = DateSerial(Year(Date) + 1, 1, 1) - DateSerial(Year(Date), 1, 1)
    
    Set rng = Range("C" & ThisYearRow1).Resize(ThisYearDays)
    If Not Intersect(Target, rng) Is Nothing Then
        MyData = rng.Value
        MyData(Target.Row - ThisYearRow1 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum distance achieved"
    End If
    
    Set rng = rng.Offset(, 1)
    If Not Intersect(Target, rng) Is Nothing Then
        MyData = rng.Value
        MyData(Target.Row - ThisYearRow1 + 1, 1) = ""
        OldMax = WorksheetFunction.Max(MyData)
        If Target.Value > OldMax Then MsgBox "Maximum time achieved"
    End If
    
End Sub

Just one other comment about the code. If more than one cell is changed at once, the code will error. eg if C8656:C8657 are selected and 'Delete' is pressed.
Is that a potential problem for you?

 

[Ironman]

Many thanks Peter - I guess there's no way of testing this before Jan 1, although in fairness, every solution you've kindly provided for me has been faultless!

The potential erroring shouldn't be a problem - I don't expect to ever amend more than one cell at once but I suppose if for some reason I did do that in 2 years' time and forgot about this, I could always post back with the error code?

 

[Peter_SSs]

I guess there's no way of testing this before Jan 1

You could test with a copy of your workbook by manually changing each of these three bits. For example, to test for a leap year replace each Year(Date) with 2024

ThisYearRow1 = DateSerial(Year(Date), 1, 1) - DateSerial(2021, 1, 1) + BaseRow
ThisYearDays = DateSerial(Year(Date) + 1, 1, 1) - DateSerial(Year(Date), 1, 1)

 

[Ironman]

Thanks Peter. I've just done that as follows

ThisYearRow1 = DateSerial(Year(2024), 1, 1) - DateSerial(2021, 1, 1) + BaseRow
    ThisYearDays = DateSerial(Year(2024) + 1, 1, 1) - DateSerial(Year(2024), 1, 1)

and this row errored 1004 Method range of object_Worksheet Failed

Set rng = Range("C" & ThisYearRow1).Resize(ThisYearDays)

 

[Peter_SSs]

I said replace Year(Date) with 2024 not replace Date with 2024

 

[Ironman]

I tried that first and it returned "Expected: end of statement" with the comma after the first "1" for ThisYearsDays highlighted for all 3.

ThisYearRow1 = DateSerial(2024), 1, 1) - DateSerial(2021, 1, 1) + BaseRow
    ThisYearDays = DateSerial(2024) + 1, 1, 1) - DateSerial(2024), 1, 1)

 

[Michael M]

Try removing the 2nd parens
DateSerial(2024)

 

[Peter_SSs]

Yes, this time you replaced Year(Date with 2024.

In post 4 take the whole red text out - nothing more, nothing less - and replace it with 2024.

 

[Ironman]

Ahh sorry, my elderly eyesight, didn't spot first bracket wasn't in red! I dragged the handle down to 2024 and yes, it works!

Thanks ever so much Peter (and Michael too for your input).