VB Code that will copy color coded cells to an invoice when activated

  • Thread starter Thread starter Skip Christensen
  • Start date Start date
S

Skip Christensen

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
 
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
 
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
 
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.
 
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.
 
Back
Top