Macro: Use cell fill color to determine if to copy & paste to another cell on the same row

Good Day all,

Given the example file I'm trying to use VBA to copy and paste values only from column I to the values in column E if:

1. The cell is green

That's it. In the actual file there are as many as 10,000 names. some of the cells in that row are red or clear and should not be copied over.

P.S. Column I is actually column BL and column E is actually column S in the real file I'm working on.

I'm stuck at work and saw this could be done in my book, but I won't be getting to that book anytime soon. Any help would be much appreciated.

Best Regards,

T. Samuels

So you Just want to copy the Values without the Cell Color and any other formatting?

Hi, tsamuels,

you should have indicated that you apply Conditional Formatting for the cell color.

Please Login or Register  to view this content.

You would need to adapt the I in Set rngArea for your Column BL instead of I here and change the E to S for the destination.

Ciao,
Holger

Hi, tsamuels,

maybe thatīs why the code is using CountIf to see if the value in the checked cell is unique in the area to check as well as over 100 before copying?

Ciao,
Holger

Hi, tsamuels,

SpecialCells(xlCellTypeConstants, 23) is looking for constants in the range, I should have suggested

Please Login or Register  to view this content.

which is looking for formulas in the range. I would recommend to use an On Error Resume Next when using SpecialCells to avoid a run-time error and after setting back with On Error GoTo 0 and checking if any error had occurred.

Ciao,
Holger

Hi, tsamuels,

if your formulas return a blank cell if nothing is found and cover the entire range itīs quite useless to try and narrow down the area by using SpecialCells with Formulas as all cells would be included. So just the range could be checked without any specialcells being added to it.

Ciao,
Holger

Hi, tsamuels,

one way to speed the macro up would be to turn off Calculation and ScreenUpdating at the beginning and turn it on again at the end:

Please Login or Register  to view this content.

or store the values in Variables and use them instead.

Another way to narrow down could be an extra column holding a formula and serving as feed for an Autofilter. From your sample workbook I choose Column J and placed the following formula into J2 and then dragged it down (double click on the lower right end of cell J2):

Please Login or Register  to view this content.

Autofilter may be used for 1 and will definitely narrow down the number of checks to fulfill if the code is applied to only visible cells via SpecialCells(xlCellTypeVisible) and a check if there are indeed cells to apply the For Each rngCell in rngArea...

Ciao,
Holger