Color set of rows according to sort.

**Ph0ny** · 07-02-2008, 01:09 PM

So, I have this sheet and VBA code.

Code look like this:

Sub Macro()
'
' B3 Macro
' Macro V.1
'

'
'---------------------------------------Find Last Row With Data-----------------------------------
Sheets("Sheet1").Select

Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
    LastRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    End If
'---------------------------------------Delete And Keep Relevant Columns-------------------------
    Range("A:A,C:C,E:F,H:I,K:U,W:BQ").Select
    Range("A1").Activate
    Selection.Delete Shift:=xlToLeft
'---------------------------------------Get NPC Date---------------------------------------------
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=MID(R[-1]C[-1],12,11)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],12,11)"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & LastRow), Type:=xlFillDefault
    Range("F2:F" & LastRow).Select
    Columns("F:F").EntireColumn.AutoFit
'---------------------------------------Filter After A3,B3 Status------------------------------------
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="A3,B3"
'---------------------------------------Sort After Route/Date--------------------------------------
    Range("A1").Select
    Range("A2").Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
    ("F2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
    :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal

End Sub

You can see what this VBA will do. I want to be able to color ever second SET of rows that match according to column F. Every second time the value in the F column change I want to color it. How to do it?

See example:

A B C D E F
1 2 3 4 5 6
1 2 3 4 5 6
7 8 9 0 1 2
7 8 9 0 1 2
3 4 5 6 7 8
3 4 5 6 7 8
3 4 5 6 7 8
9 0 1 2 3 4
9 0 1 2 3 4