Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code that will copy color coded cells to an invoice when activated
My boss wants to select certain cells and code them with
color and have them sent to different invoices depending on that color. Can I write some code or get it from somewhere that will do that for me? Thanks SkipC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code that will copy color coded cells to an invoice when activated
Send to different invoices. What does that mean
for each cell in Activesheet.UsedRange.specialCells(xlconstants) select Case cell.Interior.ColorIndex case 1 ' send to invoice case 2 ' send to invoice case 3 ' send to invoice case else end Select Next -- Regards, Tom Ogilvy Skip Christensen wrote in message ... My boss wants to select certain cells and code them with color and have them sent to different invoices depending on that color. Can I write some code or get it from somewhere that will do that for me? Thanks SkipC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code that will copy color coded cells to an invoice when activated
Thanks Tom:
Different companies are coded different colors on one "daily buy report" and I have to have that data sent to the appropriate company's invoice rather than him typing it again into the invoices. We're talking 10 different companies, "colors", 10 invoices, and as many as 20 different varieties of products, prices and Pounds. The address of the destination cells in the invoices will vary depending on the type of product, the color only tells me what invoice it goes to. I may need this code to be a little more specific if possible, Please? SkipC -----Original Message----- Send to different invoices. What does that mean for each cell in Activesheet.UsedRange.specialCells (xlconstants) select Case cell.Interior.ColorIndex case 1 ' send to invoice case 2 ' send to invoice case 3 ' send to invoice case else end Select Next -- Regards, Tom Ogilvy Skip Christensen wrote in message ... My boss wants to select certain cells and code them with color and have them sent to different invoices depending on that color. Can I write some code or get it from somewhere that will do that for me? Thanks SkipC . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code that will copy color coded cells to an invoice when activated
for each cell in Activesheet.Range("B9:B200") ' range with products/colored select Case cell.Interior.ColorIndex case 1 ' company 1, copy this cell and 4 to the right to ' the next blank cell in column C above row 50; C3 ' would have a header like "Product", so the first ' cell colored 1 in Column B starting with row 9 ' will be copied to C4 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company1").Cells(50,"C").End(xlup)(2) case 2 ' send to Company 2 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company2").Cells(50,"C").End(xlup)(2) case 3 ' send to company 3 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company3").Cells(50,"C").End(xlup)(2) case else end Select Next So you loop down column b starting in Cell B9 and determine the colorindex for the cell, then use that in the case statement to figure out where to copy it. As written it copies the cells for that row in columns B to F and puts them in the product listing portion of the Company Invoice sheet for the color coded company See Excel VBA help for color index for the color index numbers and corresponding colors from the default palette. -- Regards, Tom Ogilvy SKipC wrote in message ... Thanks Tom: Different companies are coded different colors on one "daily buy report" and I have to have that data sent to the appropriate company's invoice rather than him typing it again into the invoices. We're talking 10 different companies, "colors", 10 invoices, and as many as 20 different varieties of products, prices and Pounds. The address of the destination cells in the invoices will vary depending on the type of product, the color only tells me what invoice it goes to. I may need this code to be a little more specific if possible, Please? SkipC -----Original Message----- Send to different invoices. What does that mean for each cell in Activesheet.UsedRange.specialCells (xlconstants) select Case cell.Interior.ColorIndex case 1 ' send to invoice case 2 ' send to invoice case 3 ' send to invoice case else end Select Next -- Regards, Tom Ogilvy Skip Christensen wrote in message ... My boss wants to select certain cells and code them with color and have them sent to different invoices depending on that color. Can I write some code or get it from somewhere that will do that for me? Thanks SkipC . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code that will copy color coded cells to an invoice when activated
Your the MAN Tom:
Thanks, I think I'm getting there SkipC ----- Tom Ogilvy wrote: ----- for each cell in Activesheet.Range("B9:B200") ' range with products/colored select Case cell.Interior.ColorIndex case 1 ' company 1, copy this cell and 4 to the right to ' the next blank cell in column C above row 50; C3 ' would have a header like "Product", so the first ' cell colored 1 in Column B starting with row 9 ' will be copied to C4 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company1").Cells(50,"C").End(xlup)(2) case 2 ' send to Company 2 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company2").Cells(50,"C").End(xlup)(2) case 3 ' send to company 3 Cell.Resize(1,5).copy Destination:=Worksheets( _ "company3").Cells(50,"C").End(xlup)(2) case else end Select Next So you loop down column b starting in Cell B9 and determine the colorindex for the cell, then use that in the case statement to figure out where to copy it. As written it copies the cells for that row in columns B to F and puts them in the product listing portion of the Company Invoice sheet for the color coded company See Excel VBA help for color index for the color index numbers and corresponding colors from the default palette. -- Regards, Tom Ogilvy SKipC wrote in message ... Thanks Tom: Different companies are coded different colors on one "daily buy report" and I have to have that data sent to the appropriate company's invoice rather than him typing it again into the invoices. We're talking 10 different companies, "colors", 10 invoices, and as many as 20 different varieties of products, prices and Pounds. The address of the destination cells in the invoices will vary depending on the type of product, the color only tells me what invoice it goes to. I may need this code to be a little more specific if possible, Please? SkipC -----Original Message----- Send to different invoices. What does that mean for each cell in Activesheet.UsedRange.specialCells (xlconstants) select Case cell.Interior.ColorIndex case 1 ' send to invoice case 2 ' send to invoice case 3 ' send to invoice case else end Select Next -- Regards, Tom Ogilvy Skip Christensen wrote in message ... My boss wants to select certain cells and code them with color and have them sent to different invoices depending on that color. Can I write some code or get it from somewhere that will do that for me? Thanks SkipC . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells change color when another cell is activated or typed with an | Excel Discussion (Misc queries) | |||
Count of color coded cells? | Excel Discussion (Misc queries) | |||
Sum Color coded cells | Excel Worksheet Functions | |||
Can you sort excel data by color coded cells? | Excel Discussion (Misc queries) | |||
Color Coded Cells | Excel Programming |