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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You don't need to highlight or select anything in particular. The code knows what to do! :biggrin:

lenze
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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