Color coding by items

[jlclyde]

I have a group of data that is in A1:K10,000. The data is sorted by
item numbers, which are in A. I would like to have the code go
through and color code the changes. So if Item one is in A1:A14, I
would like this to be one color. Then on A15:A18 which is a different
item number, it woudl be a different color. I was thinking cycle
through ColorIndex 36, 39, 37, 40, 38. Coloring each item from A:K.

There is another thing that I am hoping can be answered as well. In
Column C there is a sequence number. I would like to add a border
around each sequence in each item. If in my example above Item one is
in A1:A14, sequence one is in C1:C4, sequence two woudl be in C5:C8,
sequence 3 would be in C9:C14. So it would put a border around each
sequence whithin each item. So A1:K4 would have a border. Then A5:K8
would have a border.

I know this is a lot, but is it possible. If you can give me some
basic code to get going I am sure I can figure it out. I know how to
do loops and for next but this seems a little more complicated then
that.

Thanks,
Jay

 

[Jennifer]

Maybe I don't understand what you are asking but wouldn't it work to use
"Conditional Formating" for both these

 

[jlclyde]

Maybe I don't understand what you are asking but wouldn't it work to use
"Conditional Formating" for both these
--
Thank you,

Jennifer








- Show quoted text -

I had thought about that. How would you use CF to take care of this.
You cannot put more then 3 formats in. Let me know what your thoughts
are on this.
Thanks,
Jay

 

[Jennifer]

Your right if you are going to have more that 3 differant formats that won't
work. Sorry that just seemed like an easy answer. I haven't worked much with
writing code for formating so I will be watching to see how these smart guys
and gals answer your question.

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim StartRow2 As Long
Dim aryColours As Variant
Dim idxColours As Long

aryColours = Array(36, 39, 37, 40, 38)
idxColours = LBound(aryColours)
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
StartRow2 = 1
For i = 2 To LastRow + 1

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(StartRow, "A").Resize(i -
StartRow).Interior.ColorIndex = aryColours(idxColours)
idxColours = idxColours + 1
If idxColours > UBound(aryColours) Then idxColours =
LBound(aryColours)
StartRow = i
End If

If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Or _
.Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(StartRow2, "A").Resize(i - StartRow2).BorderAround
ColorIndex:=xlColorIndexAutomatic, Weight:=xlThin
StartRow2 = i
End If
Next i

End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jlclyde]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim StartRow2 As Long
Dim aryColours As Variant
Dim idxColours As Long

    aryColours = Array(36, 39, 37, 40, 38)
    idxColours = LBound(aryColours)
    With ActiveSheet

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        StartRow = 1
        StartRow2 = 1
        For i = 2 To LastRow + 1

            If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

                .Cells(StartRow, "A").Resize(i -
StartRow).Interior.ColorIndex = aryColours(idxColours)
                idxColours = idxColours + 1
                If idxColours > UBound(aryColours) Then idxColours =
LBound(aryColours)
                StartRow = i
            End If

            If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Or _
                .Cells(i, "A").Value <> .Cells(i - 1, "A")..Value Then

                .Cells(StartRow2, "A").Resize(i - StartRow2).BorderAround
ColorIndex:=xlColorIndexAutomatic, Weight:=xlThin
                StartRow2 = i
            End If
        Next i

    End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)









- Show quoted text -

Bob,
With some minor modifications, your code works great. I had to change
the Resize to 11 so that it will include all columns. There were a
couple of line breaks due to copying and pasting into here. But all
in all great chunk of code. I like how you have it cycling though the
colors. I am putting this one in my Personal macro work book. I am
sure I will be stealing chunks of code from this for years.

Thanks,
Jay

 

[Bob Phillips]

Glad you liked it.

The line breaks that my newsreader creates are a real pain. I have a way of
trapping it, but I most often forget to run it.

The colour cycling was a last minute addition. At first I assumed you would
define all the colours that you needed, but then I got real, and catered for
it <g>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim StartRow2 As Long
Dim aryColours As Variant
Dim idxColours As Long

aryColours = Array(36, 39, 37, 40, 38)
idxColours = LBound(aryColours)
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
StartRow2 = 1
For i = 2 To LastRow + 1

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(StartRow, "A").Resize(i -
StartRow).Interior.ColorIndex = aryColours(idxColours)
idxColours = idxColours + 1
If idxColours > UBound(aryColours) Then idxColours =
LBound(aryColours)
StartRow = i
End If

If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Or _
.Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(StartRow2, "A").Resize(i - StartRow2).BorderAround
ColorIndex:=xlColorIndexAutomatic, Weight:=xlThin
StartRow2 = i
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)









- Show quoted text -

Bob,
With some minor modifications, your code works great. I had to change
the Resize to 11 so that it will include all columns. There were a
couple of line breaks due to copying and pasting into here. But all
in all great chunk of code. I like how you have it cycling though the
colors. I am putting this one in my Personal macro work book. I am
sure I will be stealing chunks of code from this for years.

Thanks,
Jay