Copying cetrtain cells in a column?

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
I have a column and I have set a cell color for certain scattered cells and I simply want to move a copy of everyone of those cells directly to it's left into an empty column...how?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
How are the cells colored. Manually or via Conditional Formatting??
lenze
 

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
I used the find and select option and I set the Find for all BOLD Cells and the Replace with a Blue cell color fill so I could differenciate them from the rest of the cells
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
If your cells are in "B" and you want to copy them to "A", use something like this
Code:
Sub CopyColor()
Dim cl As Range
For Each cl In Range("$B$2:$B" & Cells(Rows.Count, "B").End(xlUp))
If cl.Interior.ColorIndex = 5 Then cl.Copy Cells(cl.Row, "A")
Next cl
End Sub
5 is the ColorIndex for normal blue. If you need to check it, select a colored cell and run this
Code:
Sub GetCI()
MsgBox ActiveCell.Interior.ColorIndex
End Sub
Then above code will also copy the formatting. If you just want the value, then use
Code:
Sub CopyColor()
Dim cl As Range
For Each cl In Range("$B$2:$B" & Cells(Rows.Count, "B").End(xlUp))
If cl.Interior.ColorIndex = 5 Then Cells(cl.Row, "A")= cl
Next cl
End Sub
HTH
lenze
 

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468

ADVERTISEMENT

OK well I'm alittle bit of a beginner am I to assume that those are formulas that I would simply insert into one cell and drag the formula to repeat down the column
 

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
walk me through this a bit...can I just copy and paste the codes you have there in a cell next to one in question and how would I figure out what number color I am working with
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

No. These are VBA code (Macros) They go into a module. Open the VBE(Visual Basic Editor) by using ALT+F11. Choose Insert>Module. Paste or type the code in the panel. Close the VBE(ALT+Q) and run the macro. Don't forget to change the references to your cells and columns. If you need help, post back your specific Columns and Cells. To run the macro, open the Macro dialog (ALT+F8), select it and click run. Try this on a copy of your workbook just to be safe. The Sub GetCI() will get the ColorIndex of the Active cell

Only use 1 of the macros named "CopyColor", depending on what you want
 

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
Ok I have aquired the color index number now I have pasted you code with the correct color number into a module do I have to highlight both columns or just the first cell and then simply run the mudule
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
You don't need to highlight or select anything in particular. The code knows what to do! :biggrin:

lenze
 

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
I need this action for cell 2 through 4138 in column A do I have to adjust the code for that
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top