Sort data according to date and time

Hi Experts,

I would like to request Experts help create a macro to sort data at Column D. The nearest date and time need to be at top of the column and highlight the row (Range A:I) with red  color if the date at column D is less than 3 days (compare with current date) automatically. Hope Expert will help me to create this feature.

I have attached the workbook for Experts perusal.


SortDate.xls
ThevaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrainBConnect With a Mentor Commented:
I think you want to sort Ascending & highlight any date 1 to 3 days from now. This is the code.
'=============================================================================
'- SORT DATES IN DESCENDING ORDER.
'- HIGHLIGHT IF BETWEEN 1-3 DAYS FROM NOW
'=============================================================================
Sub DATE_SORT()
    Dim Lastrow As Long
    Dim Day1 As Date
    Dim Day3 As Date
    '------------------------------------------------------------------------
    Lastrow = Range("D65536").End(xlUp).Row
    Range("A3:A" & Lastrow).Cells.Interior.ColorIndex = xlNone
    Day1 = Now()
    Day3 = Now() + 3
    Range("A3:AP" & Lastrow).Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    '-------------------------------------------------------------------------
    '- LOOP ROWS
    For r = 3 To Lastrow
        If Cells(r, "D").Value > Day1 And Cells(r, "D").Value <= Day3 Then
            Cells(r, "A").Interior.ColorIndex = 6
        End If
    Next
    '------------------------------------------------------------------------
End Sub
'=============================================================================

Open in new window

0
 
Ardhendu SarangiConnect With a Mentor Sr. Project ManagerCommented:
Hi Theva,
did you mean something like this?
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("D4:D1800").Interior.ColorIndex = "0"

    ActiveWorkbook.Worksheets("Record").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Record").Sort.SortFields.Add Key:=Range("D4:D1800") _
                                                          , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Record").Sort
        .SetRange Range("A3:AP1800")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For i = 4 To Cells(65536, "D").End(xlUp).Row
        'MsgBox (Range("D" & i) - Now())
        If Range("D" & i) - Now() > 3 Then
            Range("D" & i).Interior.ColorIndex = "03"
        End If
    Next
End Sub

Open in new window

0
 
Ardhendu SarangiSr. Project ManagerCommented:
BTW, what time zone do u work from... u seems to be on all the time :)
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
dacaseyCommented:
Why do you need a macro?  You can simply sort on the date column?

Also a simple conditional format would change the color.

I have attached an image to show you the conditional format
 Excel conditional format
0
 
ThevaAuthor Commented:
Hi pari123,

Thanks for the code. Perhaps my Explanation could create confusion, sorry for that. Managed to sort for date sorting, by right should "Order:=xlAscending", but not sure how to modify <3days. We have buffer 3 days, if the date crossed this 3 days rules than we need to highlight.  e.g. today (17-Nov), therefore if there is  data at cell between 18 - 20, need to highlight, ignore other date (20 >). Hope its clear.

P/s: I'm GMT+8 time actually.  
0
 
TommySzalapskiConnect With a Mentor Commented:
Change the code to read
If Range("D" & i) - Now() <= 3 And Range("D" & i) >= Now() Then
            Range("D" & i).Interior.ColorIndex = "03"
        End If

Open in new window

0
 
ThevaAuthor Commented:
Hi Tommy,

No date being highlighted. How to code "Now() + 3 day" highlight the data. If more than 3 days ignore.

0
 
ThevaAuthor Commented:
Hi,
Thanks for the great help.
0
All Courses

From novice to tech pro — start learning today.