Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Highlight Entire Row based on single cell value

This is a discussion on Highlight Entire Row based on single cell value within the Excel Questions forums, part of the Question Forums category; HELP! I have a huge spreadsheet that i need to select a bunch of rows. the rows i need to ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    20

    Default Highlight Entire Row based on single cell value

    HELP! I have a huge spreadsheet that i need to select a bunch of rows. the rows i need to highlight are contingent on data in a single cell in column AA. How do I do this?

    Once I highlight the rows, I would like to copy and paste in another sheet and maintain the blank rows that were not highlighted...make sense?

    THANKS! and happy monday :/

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Highlight Entire Row based on single cell value

    Maybe like this- change the value in red to suit

    Code:
    Sub CopyRows()
    Dim c As Range, r As Range
    For Each c In Columns("AA").SpecialCells(xlCellTypeConstants)
        If c.Value = "Copy" Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
    Next c
    If Not r Is Nothing Then
        With r.EntireRow
            .Copy Destination:=Sheets("Sheet2").Range("A1")
            .Delete
        End With
    End If
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    20

    Default Re: Highlight Entire Row based on single cell value

    ehhhh i have no idea what that means....do you have laymens terms?

    im sorry- such a pain.

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Highlight Entire Row based on single cell value

    Press ALT + F11 to open the Visual Basic Editor. Select Module from the Insert menu then paste the code into the white space on the right. Change my red Copy to your criterion in column AA. Press ALT + Q to close the code window.

    Press ALT + F8 then double click CopyRows in the dialog box which should appear.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    20

    Default Re: Highlight Entire Row based on single cell value

    When I do this I get an error message and when i click DEBUG it brings me back to the Microsof Visual Basic and ".Copy Destination:=Sheets("Sheet2").Range("A1")" is Highlighted yellow.

    any suggestions?

  6. #6
    New Member
    Join Date
    Mar 2011
    Posts
    20

    Default Re: Highlight Entire Row based on single cell value

    OH WAIT! I got it but when I go to the new sheet the spacing is not right. All rows are filled. i wanted the blank rows where the row was not selected, make sense?

  7. #7
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Highlight Entire Row based on single cell value

    You will need to change Sheet2 to the name of the sheet to copy to. This sheet must exist.
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Highlight Entire Row based on single cell value

    Try like this

    Code:
    Sub CopyRows()
    Dim c As Range, r As Range
    For Each c In Columns("AA").SpecialCells(xlCellTypeConstants)
        If c.Value = "Copy" Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
        c.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & c.Row)
    Next c
    If Not r Is Nothing Then r.EntireRow.Delete
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    New Member
    Join Date
    Mar 2011
    Posts
    20

    Default Re: Highlight Entire Row based on single cell value

    Didn't work the first one was right but the spacing is wrong. for example: if I'm copying rows 2,5,6,7,10 then in the new spreadsheet I only want data in those rows. and row 1,3,4,8,9 will be blank. is that possible?

    seriously thank you so much for your help!

  10. #10
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Highlight Entire Row based on single cell value

    It works for me.
    HTH, Peter
    Please test any code on a copy of your workbook.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com