+ Reply to Thread
Results 1 to 3 of 3

How To Copy Entire Row When Red Cell Found

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

    How To Copy Entire Row When Red Cell Found

    Hello, Hopefully someone can help with a simple solution.
    I got the following coding from a fellow user on here with a previous question and I'm in a bind now. This following code looks for red cells in column A then copies From Column A and the remaining contents to the next sheet. I use the same code in a different part of my Macro except this time I need it to search Column C for the red cell. I need to know which part of the coding I need to change to make sure when it finds a red cell to copy the entire row and not just from the active Column. Because right now with the modified version it copies from Column C and so on. Thank You.

    Original Coding:
    Sub test()
    Dim r As Range, ff As String, x As Range
    With Application.FindFormat
    .Clear
    .Interior.Color = vbRed
    End With
    With Sheets("sheet1").Columns(1)
    Set r = .Find("*", searchformat:=True)
    If Not r Is Nothing Then
    ff = r.Address: Set x = r.Resize(, 9)
    Do
    Set x = Union(x, r.Resize(, 9))
    Set r = .Find("*", r, searchformat:=True)
    Loop Until ff = r.Address
    x.Copy Sheets("sheet2").Cells(1)
    End If
    End With
    End Sub

    Modified Coding to search Column C:
    Sub CopyActualDupes()
    Dim r As Range, ff As String, x As Range
    With Application.FindFormat
    .Clear
    .Interior.Color = vbRed
    End With
    With Sheets("sheet2").Columns(3)
    Set r = .Find("*", searchformat:=True)
    If Not r Is Nothing Then
    ff = r.Address: Set x = r.Resize(, 9)
    Do
    Set x = Union(x, r.Resize(, 9))
    Set r = .Find("*", r, searchformat:=True)
    Loop Until ff = r.Address
    x.Copy Sheets("sheet3").Cells(1)
    End If
    End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How To Copy Entire Row When Red Cell Found

    Please use code tags with your code

    Please Login or Register  to view this content.

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

    Re: How To Copy Entire Row When Red Cell Found

    Thank You! Simple Enough.

+ 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