Help with my VBA find match from 1 criteria then colour row, to 4 criteria and colour rows

Good afternoon

Windows 10
Excel 2019


I have been trying to work this out for days now but I am stuck and banging my head against a wall.

The existing VBA code (called as a Macro) below looks down Colum K (starting with K2) and for each unique set of entry’s in Column K it colours the background of each unique set using one of two alternating colours to aid readability.

This works perfectly but the criteria have changed from a single column match to a match across 4 Columns downwards.

It needs to be where (working down the work sheet),

Column A (Fruit) + Column B (Product Type) + Column J (Units) + Column K (Orders)

match on multiple rows then colour each matching set of row use the alternating 2 colours as shown in the code.

My VBA knowledge is limited and I have been unable to find a way to have more than the one existing criteria.

I attach a example worksheet (zip file) of what I am trying to achieve.

Please Login or Register  to view this content.

Attachment 863336

Try below code

PHP Code: 

Option Explicit
Sub colour()
Dim lr&, i&, c&, rng
Dim u As Range, st1$, st2$
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A2:K" & lr)
    .Interior.Color = RGB(255, 255, 197) ' set all to 1st color
    rng = .Value ' copy value into array
End With
For i = 2 To UBound(rng) ' loop from row 2 only
    st1 = rng(i - 1, 1) & "|" & rng(i - 1, 2) & "|" & rng(i - 1, 10) & "|" & rng(i - 1, 11) ' previous combi
    st2 = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 10) & "|" & rng(i, 11) ' current combi
    If st2 <> st1 Then c = c + 1 'count the different combi
    If c Mod 2 = 1 Then ' c = 1,3,5,7...
        If u Is Nothing Then ' create an union of cells: A5:K5 + A6:K6 + ...
            Set u = Range(Cells(i + 1, "A"), Cells(i + 1, "K"))
        Else
            Set u = Union(u, Range(Cells(i + 1, "A"), Cells(i + 1, "K")))
        End If
    End If
Next
If u Is Nothing Then Exit Sub
u.Interior.Color = RGB(179, 230, 255) ' color the union
End Sub