+ Reply to Thread
Results 1 to 14 of 14

Macro To Find Cell Color and Copy to Sheet 2

  1. #1
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Macro To Find Cell Color and Copy to Sheet 2

    Hello, Hoping someone can help me. I'm trying to create a macro that will locate a red cells in Column A then copy that row into sheet 2. It will then go back to sheet 1 and continue down column A and do the same process if it comes across another red Cell. I created a macro to find duplicates so the cells are not being changed by conditional formatting and this macro being created would be the next process. I know I can probably do this with conditional formatting or another manual way but I will have this program running in the background while I am working and do not need to be clicking a button for each step. I will have roughly 65k files in column A so I know this will take some time to go through and that is another reason why I would like to not have to do it manually. Any help is appreciated I've tried several options but none of them are what I'm looking for. Any information is greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro To Find Cell Color and Copy to Sheet 2

    is your cell color from conditional format or actually coded in from formatting?

    if actually coded try this method
    http://datapigtechnologies.com/blog/...in-excel-2003/
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Macro To Find Cell Color and Copy to Sheet 2

    It's actually coded. Before I run this macro I run a macro to find duplicates and it changes the cell color for me. Can you resend that link it won't come up for me. Thanks for your help.

  4. #4
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Try this simple code. You may need to change the RGB tint and you might wish to change to a FOR NEXT loop, if you know exactly how many rows you have in sheet 1.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro To Find Cell Color and Copy to Sheet 2

    http://datapigtechnologies.com/blog/...in-excel-2003/

    if your list is sorted then this is a simple macro from MS for finding duplicates

    http://support.microsoft.com/kb/213355
    Last edited by humdingaling; 06-16-2013 at 09:26 PM.

  6. #6
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Humdingaling thanks for the response unfortunately that isn't quite what I'm looking for since it just sorts by cell color.

    Ed_Collins I tried your coding but I'm getting an error: Here's my current coding so you can see what I'm working with. It a combination of macros I've found online and changed it to fit my needs.

    I have these macros being called in the main portion. So FindDups finishes then the next line calls for DuplicateCopy. I apologize if I'm misunderstand any of these responses and I appreciate the help.

    This is the Macro that finds the dupes and changes the cell color to red:
    Sub FindDups()
    '
    ' NOTE: You must select the first cell in the column and
    ' make sure that the column is sorted before running this macro
    '
    ScreenUpdating = False
    FirstItem = ActiveCell.Value
    SecondItem = ActiveCell.Offset(1, 0).Value
    Offsetcount = 1
    Do While ActiveCell <> ""
    If FirstItem = SecondItem Then
    ActiveCell.Offset(0, 0).Interior.Color = RGB(255, 0, 0)
    ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
    Offsetcount = Offsetcount + 1
    SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
    Else
    ActiveCell.Offset(Offsetcount, 0).Select
    FirstItem = ActiveCell.Value
    SecondItem = ActiveCell.Offset(1, 0).Value
    Offsetcount = 1
    End If

    Loop
    ScreenUpdating = True
    End Sub

    Here is the Macro I need and what I have so far according to the responses:
    Sub DuplicateCopy()
    sheet1_row = 1: sheet2_row = 0

    Do Until Sheet1.Cells(sheet1_row, 1) = ""
    If Sheet1.Cells(sheet1_row, 1).Interior.Color = RGB(255, 0, 0) Then ' <---- change the rgb tint as desired
    sheet2_row = sheet2_row + 1
    Sheet2.Cells(sheet2_row, 1) = Sheet1.Cells(sheet1_row, 1)
    End If
    sheet1_row = sheet1_row = 1 + 1
    Loop

    End Sub

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro To Find Cell Color and Copy to Sheet 2

    sorry being a bit lazy there

    Please Login or Register  to view this content.
    now i assumed second sheet be empty
    if it is not...assign DupRow to be Range("A65536").End(xlUp).Row (assuming column A is being used) instead of 2
    Last edited by humdingaling; 06-16-2013 at 10:04 PM.

  8. #8
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Yea, I was getting an error too, and I don't know why. (That code was untested.)
    Try this. This works for me.

    It will check column A for all of your 65,000 rows (change as needed) looking for a red cell.
    When found, it copies the data in that cell to Sheet 2.

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Thanks Humdingaling. This is exactly what I need. I just need one more portion. That code copies just column A I have information from columns A to I. What would be the code to get it to copy the entire row and paste it into sheet 2. Thanks again this is helping alot.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Try this
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Please Login or Register  to view this content.
    amended code to copy A to I
    Range(Cells(cRow, 1), Cells(cRow, 9))
    9 = I, if you need more of the row copied just change this time
    Last edited by humdingaling; 06-16-2013 at 10:25 PM. Reason: moar info

  12. #12
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Hey Jindon, I tried this coding and it worked for one portion. I then pasted it in another portion but I'm having trouble. I want it to copy the whole row and not just from the red cell. Because in another portion I am checking Column C for red cells and then would copy the whole row to another sheet. But right now it is only copying from Column C. Any Suggestions. Thanks for your help.

    Quote Originally Posted by jindon View Post
    Try this
    Please Login or Register  to view this content.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Do you mean like this?
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Macro To Find Cell Color and Copy to Sheet 2

    Yes. Thank You!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1