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

Thanks a lot, you are a genius!

Is it possible to get only the date-cells (thus column N) to be coloured, and not the entire row? And instead of the bright green colour I would like the more calm green colour (the one next to the bright one in the colour field of excel)

Kind regards,

Renate

Answer 2

About the colors:

If you run this macro (p.e. in "Sheet2") you can make your choise.

The rownumber is the colornumber.

Sub colorindices() 

For y = 1 To 56 

Sheets("Sheet2").Range("a" & y).Interior.ColorIndex = y 

Next y 

End Sub

For only to color colomn N (in your last message you wrote N = date-column), use this macro and change

the colornumbers 3, 4, and 46 to your preference.

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

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

Range("n" & lr).Interior.ColorIndex = -4142 

For x = 2 To lr 

With Range("n" & x) 

Select Case Month(Range("n" & 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("n" & x)) 

Case Is > Year(Date) 

.Interior.ColorIndex = 4 

Case Is < Year(Date) 

.Interior.ColorIndex = 3 

End Select 

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

.Interior.ColorIndex = -4142 

End If 

End With 

Next x 

End If 

Application.ScreenUpdating = True 

End Sub

Answer 3

Renate,

Has your problem been solved?

Answer 4

Hi,

Thank you for your answer.

I am however not able to make it work, am I supposed to type in both of the codes you have sent me?

This sheet is nr.2 and the date column in placed in column M. I do however also have a comment field in column N, thus the data expands column A - N and row 2 - 54 (however more equipment can be added/removed thus the total number of rows can change).

This is what I am typed into the VBA field:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Integer

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

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

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

End If

End Sub

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("m" & lr).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 = 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

It automatically came a line between the two codes, is that correct?

Kind regards,

Renate Bringsli