CODEWITHSUNDEEP
excelms-accesssplitvba
Annap
Annap

Reputation: 19

Separate when different rows in excel list appears

I have a problem with an excel spreadsheet. I'm importing data from Access to a spreadsheet which I'm using as a picking list for my employees. It's sorted by transport numbers and I would like the spreadsheet to separate the rows when a different transport number appears in another colour.

Like this:

Transportnumber:

Is there any possibilities doing this? The list contains about 10 more columns with data so it's important to make it easy for the employee to read when another different transport appears.

Upvotes: 1

Views: 115

Answers (1)

user3514930
user3514930

Reputation: 1717

If you have agree to have only two colors, using VBA:

Dim Tmp As String
Dim Colo As Double

Tmp = ""
Colo = 14470546
For i = 2 To 9999
    If Cells(i, 1).Value = "" Then Exit For
    If Cells(i, 1).Value <> Tmp Then
        If Colo = 14470546 Then
            Colo = 9737946
        Else
            Colo = 14470546
        End If
        Tmp = Cells(i, 1).Value
    End If
'    Color the row
'    Range(i & ":" & i).Interior.Color = Colo
'    Color SOME columns.
    Range("A" & i & ":L" & i).Interior.Color = Colo
Next

If you are not sorted or you want different colors you need to use a array of colors... If you have blanks you need to add some other check... This it's only a starting point.
This code already start from A2. The columns you change in the last Range (now I write L)...

Upvotes: 1

Related Questions