AutoSort by cell color and ole date to new date

**indhrui** · 09-14-2020, 04:04 AM

Hi Friends,

I am new to excel VB, i am trying to create auto sort by cell colour and expiry date when date saved or new data entered. i tried but i can't succeeded so please help me.

I had attached the sample document.....

**maniacb** · 09-15-2020, 07:46 PM

Try by adding this code to the Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7:I506")) Is Nothing Then
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add(Range( _
        "G7:G506"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
        = RGB(255, 0, 0)
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("I7:I506"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If
End Sub