Copy and paste data based on cell color

Steves73

Board Regular
Joined
Oct 19, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Hi Guys<o:p></o:p>
I am Looking for amacro that copies and paste data based on cell color<o:p></o:p>
The macro searchesrows in sheet 1 for highlighted cells (Color Index 3,4,6,8)<o:p></o:p>
If a cell is highlightedwith any of the above color index, it copies data from column A and B and Row 1pastes to Sheet 2 starting at A1, next blank row<o:p></o:p>
Please see examplebelow<o:p></o:p>
Sheet 1<o:p></o:p>
A<o:p></o:p>
B<o:p></o:p>
C<o:p></o:p>
D<o:p></o:p>
E<o:p></o:p>
F<o:p></o:p>
G<o:p></o:p>
H<o:p></o:p>
1<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
1-Mar<o:p></o:p>
2-Mar<o:p></o:p>
3-Mar<o:p></o:p>
4-Mar<o:p></o:p>
5-Mar<o:p></o:p>
6-Mar<o:p></o:p>
2<o:p></o:p>
Peter<o:p></o:p>
1<o:p></o:p>
34<o:p></o:p>
91<o:p></o:p>
148<o:p></o:p>
205<o:p></o:p>
262<o:p></o:p>
319<o:p></o:p>
3<o:p></o:p>
Paul<o:p></o:p>
2<o:p></o:p>
56<o:p></o:p>
113<o:p></o:p>
170<o:p></o:p>
227<o:p></o:p>
284<o:p></o:p>
341<o:p></o:p>
4<o:p></o:p>
Bill<o:p></o:p>
3<o:p></o:p>
67<o:p></o:p>
124<o:p></o:p>
181<o:p></o:p>
238<o:p></o:p>
295<o:p></o:p>
352<o:p></o:p>
5<o:p></o:p>
Greg<o:p></o:p>
4<o:p></o:p>
21<o:p></o:p>
78<o:p></o:p>
135<o:p></o:p>
192<o:p></o:p>
249<o:p></o:p>
306<o:p></o:p>
6<o:p></o:p>
Ian<o:p></o:p>
5<o:p></o:p>
3<o:p></o:p>
60<o:p></o:p>
117<o:p></o:p>
174<o:p></o:p>
231<o:p></o:p>
288<o:p></o:p>
7<o:p></o:p>
Gary<o:p></o:p>
6<o:p></o:p>
78<o:p></o:p>
135<o:p></o:p>
192<o:p></o:p>
249<o:p></o:p>
306<o:p></o:p>
363<o:p></o:p>
8<o:p></o:p>
Simon<o:p></o:p>
7<o:p></o:p>
90<o:p></o:p>
147<o:p></o:p>
204<o:p></o:p>
261<o:p></o:p>
318<o:p></o:p>
375<o:p></o:p>
Sheet 2<o:p></o:p>
A<o:p></o:p>
B<o:p></o:p>
C<o:p></o:p>
D<o:p></o:p>
E<o:p></o:p>
F<o:p></o:p>
G<o:p></o:p>
H<o:p></o:p>
1<o:p></o:p>
Bill<o:p></o:p>
3<o:p></o:p>
3-Mar<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
2<o:p></o:p>
Greg<o:p></o:p>
4<o:p></o:p>
4-Mar<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
3<o:p></o:p>
Ian<o:p></o:p>
5<o:p></o:p>
1-Mar<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
4<o:p></o:p>
Simon<o:p></o:p>
7<o:p></o:p>
6-Mar<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
5<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
6<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
7<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
8<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<tbody> </tbody>
<o:p>

</o:p>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
try this

Code:
Sub steve73()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngROW As Long, lngCOL As Long
Dim rng As Range, cell As Range
Set ws1 = Sheets("Steves73-1")
Set ws2 = Sheets("Steves73-2")
With ws1
    lngROW = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    lngCOL = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
    Set rng = ws1.Range(ws1.Cells(2, 3), ws1.Cells(lngROW, lngCOL))
    For Each cell In rng
        cell.Select
        Select Case cell.Interior.ColorIndex
            Case 3, 4, 6, 8
                Set rngcopy = ws1.Range(ws1.Cells(cell.Row, 1), ws1.Cells(cell.Row, 2))
                rngcopy.Copy
                With ws2
                    lngROW = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
                    ws2.Range("A" & lngROW + 1).PasteSpecial xlPasteAll
                   ws1.Cells(1, cell.Column).Copy ws2.Range("C" & lngROW + 1)
                End With
        End Select
    Next cell
End With
End Sub
 
Upvote 0
Hi Mate

First, Apologies, I had formatted the table on my first post with color of cells to copy, but they were deleted when I copied to the forum. So I have highlighted the text to represent the highlighted cells.

Your code is sort of what I need, however it is only copying cells in Column A, and B, I need the date that the highlighted cells corresponds to in row 1 to be copied to Sheet 2 as well

So it search's Sheet 1 (Column D to Column H) when it sees a highlighted cell in any of the rows it copies the data in Column A and B and also the date in row 1 that the cell corresponds to

Sheet 1
ABCDEFGH
1 1-Mar2-Mar3-Mar4-Mar5-Mar6-Mar
2Peter13491148205262319
3Paul256113170227284341
4Bill367124181238295352
5Greg42178135192249306
6Ian5360117174231288
7Gary678135192249306363
8Simon790147204261318375
Sheet 2
ABCDEFGH
1Bill33-Mar
2Greg44-Mar
3Ian51-Mar
4Simon76-Mar
5
6
7
8

<tbody>
</tbody><colgroup><col span="9"></colgroup>

I hope this helps
 
Upvote 0
yeah the code copies the date in my test book. You are saying that it doesn't copy the dates at all for you?
 
Upvote 0
Hi Mate

No, the only cells it would copy was other cells I had manually highlighted down the page, I have now deleted them however now it will not copy any cells in the range.

Will try copy and pasting the code again
 
Upvote 0
Ok, tried again. Still no luck.

It seems to only referencing cells highlighted in column A, and then it copies those cells. Doesn't seems to be referencing highlighted cells in the range - Column D - H
 
Upvote 0
why would there be cells in Column A to check? the data set example you gave has the following:

Column A=Name
Column B = some kind of employee number
Columns C-*= the data set to be checked.

Row 1 is Date

Row 2 down to *= the data set.
 
Upvote 0
I did a check, I highlighted cells in Column A to find where the code was referencing, The code copied those cell, I then deleted them and highlighted cells in the Range Column C - H, the code did not reference them and such nothing was copied.

I will open a new workbook and try again and the current book has formulas so that may be affecting it

Will let you know
 
Upvote 0
Hi Mate

Yep, works perfectly on in a new workbook so must be the formulas

Can the code ignore formulas?
 
Upvote 0

Forum statistics

Threads
1,214,638
Messages
6,120,674
Members
448,977
Latest member
moonlight6

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