select, copy and past all cells with specific colour

P

pswanie

i need to do either one of two things...

i got about 200 cells that i need to copy and paste (from column n to c).
this is a part of the recorded macro
but the list continue to about "N380"
-----------------------------------------------------------------------------------------------
Range("N5,N8,N11,N14,N17,N20,N23,N26,N29,N32,N35").Select
-----------------------------------------------------------------------------------------------

after i copy those i need to paste it to column a (thats the closing stock
and it needs to cary over to the opening stock column.

************************************************************
1. can i select all cells with yellow colour and paste programaticly?

2. should i make that a range? (i did not do ranges before. any
help/sugestions?)
************************************************************

i also need to clear a set of cells. will colour that pruple or name that
aswell.
 
J

Joel

NewRowCount = 1
Lastrow = Range("N" & Rows.Count).End(xlUp).Row
For RowCount = 1 To Lastrow
If Range("N" & RowCount).Interior.ColorIndex = 6 Then
Range("A" & NewRowCount) = Range("N" & RowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
 
P

pswanie

where would i get a list for the colours? i take it yellow = 6? blue?
purple? green?

i will try this that you gave now now
 
P

pswanie

that does not do anything?... i dont see where in this it say to copy N
and paste it to A?...


thanx

Phillip
 
J

Joel

You don't always have to do a copy, an equal sign will do the equivalent. I
get my color indxes by going to record a macro and then picking a color and
filling in a cell.. I haven't found a good place that lists all the colors.
 
P

pswanie

i did record what colour im using. number 8

then i took a button from the forms toolbar. right click and asign macro.

but clicking the button does nothing....



************************************************************
this is what i got in there

Sub Button1_Click()
NewRowCount = 1
Lastrow = Range("N" & Rows.Count).End(xlUp).Row
For RowCount = 1 To Lastrow
If Range("N" & RowCount).Interior.ColorIndex = 8 Then
Range("A" & NewRowCount) = Range("N" & RowCount)
NewRowCount = NewRowCount + 1
End If
Next RowCount
End Sub
************************************************************
 
J

Joel

color index 8 is a light blue. I doubled check the code and everything look
ok. I would put a breakpoint in the code using F9, and then activate yor
button. When the code gets to the break point then step through the code
with F8.
 
P

pswanie

jip... light blue. and not column a but c

each of these cells are light blue

N5,N8,N11,N14,N17,N20,N23,N26,N29,N32,N35... ...N380

and they need be paste in

C5,C8,C11,C14,C17,C20,C23,C26,C29,C32,C35... ...C380
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top