Sorting dates in Excel File - (Moved)

[junkew]

Not sure how big your data is but if you want to speed up you could try to work with the filter and visible ranges

No need to iterate row by row example in VBA

Sub example()
    Copy2NewColoredSheet "Red", 255
    Copy2NewColoredSheet "Blue", 16711680
    Copy2NewColoredSheet "Green", 65280
End Sub
Sub Copy2NewColoredSheet(strFiltervalue, color)
    Application.CutCopyMode = False
    Worksheets(1).Cells.AutoFilter Field:=2, Criteria1:=strFiltervalue

    giveRangeAColor Worksheets(1).Cells.SpecialCells(xlCellTypeVisible), color
  
    Worksheets(1).Cells.Copy
    Set newWS = Worksheets.Add(After:=Worksheets(1))
    newWS.Paste
    
    giveRangeAColor newWS.UsedRange, color
End Sub
Sub giveRangeAColor(r, color)
        With r.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .color = color
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

 

[don00]

hi @Nine

 

one more quick question please, so i have added elseIf statement also to complete my task, which needs to do, if there is "VNO", AND,  " empty cell in the same row but in the last column >> then color it green, copy it to (Target green),  and write comment  (OK) at the end of the column (at least 1 column after the last one).

but i have an issue combining the (AND) in the if statement, also in placing the comment after the last comment, here is what i did till now, which gives error.

ElseIf StringInStr($oWsSource.Range("A" & $i).Value, "VNO", $STR_CASESENSE) And $sLastColumn= " "  Then
        $iOut += 1
        $oRange = $oWsTargetGreen.Range("A" & $iOut & ":" & $sLastColumn & $iOut)
        _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $sLastColumn & $i, $oRange, False, $xlPasteAll)
        $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 10
        _Excel_RangeWrite($_oWorkbook, $oWsSource, "Ok", $sLastColumn & $i)


    EndIf

Next

 

 

[don00]

sorry for typing mistake, the comment (ok) should be at the end of the row (at least 1 column after the last one).