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

Hi again,

I made it work for sheet 2 in my excel file (where the date column is placed in column M). However, for sheet 1 (where the date column is placed in column L), I am not able to make it work although I have been trying to modify the coding to fit this column instead.

The code I am using for sheet 1 is as following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Integer

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

If Not Intersect(Range("l2:l" & lr), Target) Is Nothing Then

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

End If

Dim lr2 As Integer, x As Integer

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

Application.ScreenUpdating = False

If Not Intersect(Range("l2:l" & lr2), Target) Is Nothing Then

Range("a1:l" & lr2).Sort Key1:=Range("l1"), Order1:=xlAscending, Header:=xlYes

Range("l" & lr2).Interior.ColorIndex = -4142

For x = 2 To lr2

With Range("l" & x)

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

Case Is < Month(Date)

.Interior.ColorIndex = 3

Case Is = Month(Date)

.Interior.ColorIndex = 46

Case Is > Month(Date)

.Interior.ColorIndex = 10

End Select

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

Case Is > Year(Date)

.Interior.ColorIndex = 10

Case Is < Year(Date)

.Interior.ColorIndex = 3

End Select

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

.Interior.ColorIndex = -4142

End If

End With

Next x

End If

Application.ScreenUpdating = True

End Sub

Do you have any idea what the problem is ? when I run it it stops in row 13 of the coding, this get marked in yellow.

Kind regards,

Renate

Answer 2

Do you have any idea what the problem is ?

Yes, I have, I will tell you that.

But first tell me: how many columns do you use in Sheet1 en how many columns in Sheet2 ?

Answer 3

There are columns from A to M in sheet 1. A-K concerns with instrument/maintenance info, L is the date column and M is a comment-column. In sheet 2 there are a total of N columns, where M is the date column (also last column here is for comments).

I also struggle with the orange color, it turns out light green. Orange should correspond to "46" as you suggested, however this does not work, I have also changed it to "45" etc. without seeing any difference in color. This regards the dates which are placed one month in advance, ex. dates in december should be coloured orange now that we are in november.

I am looking forward to recieving your solution, thanks in advance.

Kind regards,

Answer 4

1. Go to the vba-editor via Shift-F11 (=hold down the left-Shift key and tap the F11-Key).

In the left column of the vba-editor: double-click Sheet1(Sheet1), remove all macro's at the right side and place there this macro:

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("L2:L" & lr), Target) Is Nothing Then 

Range("A1:M" & lr).Sort Key1:=Range("L1"), Order1:=xlAscending, Header:=xlYes 

Columns("A:M").Interior.ColorIndex = -4142 

For x = 2 To lr 

With Range("L" & x) 

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

Case Is < Month(Date) 

.Interior.ColorIndex = 3 

Case Is = Month(Date) 

.Interior.ColorIndex = 45 

Case Is > Month(Date) 

.Interior.ColorIndex = 43 

End Select 

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

Case Is > Year(Date) 

.Interior.ColorIndex = 43 

Case Is < Year(Date) 

.Interior.ColorIndex = 3 

End Select 

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

.Interior.ColorIndex = -4142 

End If 

End With 

Next x 

End If 

Application.ScreenUpdating = True 

End Sub

-Make sure there are no other macros at the right side, starting with "Private Sub Worksheet_Change…"

2. In the left-column of the vba-editor: double-click Sheet2(Sheet2), remove all macro's at the right side and place there this macro: 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:N" & lr).Sort Key1:=Range("M1"), Order1:=xlAscending, Header:=xlYes Columns("A:N").Interior.ColorIndex = -4142 For x = 2 To lr With Range("M" & x) Select Case Month(Range("M" & x).Value) Case Is < Month(Date) .Interior.ColorIndex = 3 Case Is = Month(Date) .Interior.ColorIndex = 45 Case Is > Month(Date) .Interior.ColorIndex = 43 End Select Select Case Year(Range("M" & x)) Case Is > Year(Date) .Interior.ColorIndex = 43 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

• Make sure there are no other macros at the right side, starting with "Private Sub Worksheet_Change…"
• Close the vba-editor and make changes in the columns L (Sheet1) and M ( Sheet2) to assess the operation of the macros
• In the macro for Sheet1, your mistake was: Range("l" & lr2).InteriorColorindex….

That should have been: Range("l2:l" & lr2).Interior.Colorindex…

• In the macro, now I used capital letters for the columns for clarity and to ensure that all columns contain no color when the macro starts, the macro makes the columns A:M (Sheet1) and A:N (Sheet2) blank.
• I don't understand why you split my macro into two parts, it's better not to do that.
• My macro "Sub colorindices()" was intended to show you all the colors belonging to the color indices. After running that macro, you could see all the colors in column A and the rownumbers left of the column are the colororindexnumbers. But you dont really need that macro, you also can see the colors and colornumbers in the image below. In the macro's I used now the colorindexnumbers 3 (red), 45 (orange) and 43 (green). You can make your own choise with the image below.

Answer 5

Renate,

Problem solved now?