How to automatically sort data collections based on date

Question

Hello,

I have an excel file with information regarding equipment, having one equipment per row.

Information about the equipment expands from column A to N, where N is a variable date (date for next maintenance service of equipment, once maintenance occur this date is updated with the date for the next maintenance to occur).

I want the data collection to be sorted by "next maintenance date", but I am not able to get this to happen automatically when the date is changed. When I update the next maintenance date for an equipment (in the sorted list), the equipment row will not locate into its new correct position (based on date).

Is there a solution to this problem?

Thanks in advance.

Kind regards,

Renate

Answer 1

This works perfectly, thanks a lot! The dates are in column M by the way, I did not inform you correctly, but I was able to modify the script accordingly myself.

I have another challenge for you if you are up for it, this is also something that would be highly useful.

It is desired that the maintenance dates are coloured differently based on urgency, and this should also occur automatically. By colouring I mean the cell filling, not the text.

Overdue maintenance dates shall be coloured in red. For example now that we are in november, all dates for october and backwards shall be marked in red. The maintenance dates which are in november shall be marked in orange, as these need to be performed this month and are critical. Maintenance dates occuring one month from now and forwards (thus december at current time) should be marked in green as these are non-critical at current time.

Kind regards,

Renate

Answer 2

Instead of the macro above, try this and note that the last non-empty row in column A determines the number of rows that are sorted and colored.

Private Sub Worksheet_Change(ByVal Target As Range) 

Dim lr As Integer, x As Integer 

lr = Range("a" & Rows.Count).End(xlUp).Row 

Application.ScreenUpdating = False 

If Not Intersect(Range("m2:m" & lr), Target) Is Nothing Then 

Range("a1:m" & lr).Sort Key1:=Range("m1"), Order1:=xlAscending, Header:=xlYes 

Range("a1:m" & lr).Interior.ColorIndex = -4142 

For x = 2 To lr 

With Range("a" & x & ":m" & x) 

Select Case Month(Range("m" & x).Value) 

Case Is < Month(Date) 

.Interior.ColorIndex = 3 

Case Is = Month(Date) 

.Interior.ColorIndex = 46 

Case Is > Month(Date) 

.Interior.ColorIndex = 4 

End Select 

Select Case Year(Range("m" & x)) 

Case Is > Year(Date) 

.Interior.ColorIndex = 4 

Case Is < Year(Date) 

.Interior.ColorIndex = 3 

End Select 

If IsEmpty(Range("m" & x)) Then 

.Interior.ColorIndex = -4142 

End If 

End With 

Next x 

End If 

Application.ScreenUpdating = True 

End Sub

Edited 09-11-2023, 13:02

Answer 3

I changed the macro above.

Now, the code is correct, I think.