Color coding by items
 

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

Color coding by items
 

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

Jennifer


"jlclyde" wrote:

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

Color coding by items
 

On May 19, 11:14*pm, Jennifer
wrote:
Maybe I don't understand what you are asking but wouldn't it work to use
"Conditional Formating" for both these
--
Thank you,

Jennifer



"jlclyde" wrote:
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- Hide quoted text -

- 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

Color coding by items
 

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.
--
Thank you,

Jennifer


"jlclyde" wrote:

On May 19, 11:14 pm, Jennifer
wrote:
Maybe I don't understand what you are asking but wouldn't it work to use
"Conditional Formating" for both these
--
Thank you,

Jennifer



"jlclyde" wrote:
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- Hide quoted text -

- 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

Color coding by items
 

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" wrote in message
...
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

Color coding by items
 

On May 20, 3:47*am, "Bob Phillips" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

Color coding by items
 

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)



"jlclyde" wrote in message
...
On May 20, 3:47 am, "Bob Phillips" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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