Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
color coding | Excel Discussion (Misc queries) | |||
Color coding cells | Excel Discussion (Misc queries) | |||
Color Coding Cells | Excel Worksheet Functions | |||
color coding | Excel Worksheet Functions | |||
Color coding | Excel Discussion (Misc queries) |