Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select cells by color (excel97 +)

    I have a long column of data with different cell backgrounds---I want select and then copy all cells with background -say "red" to a new sheet - this should be a simple macro but my brain aint there anymore!!!
    Thanx
    Smbs

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    Is something like this what you want:

    <pre>Public Sub SelectRed()
    Dim bNotFirst As Boolean
    Dim oCell As Range
    bNotFirst = False
    For Each oCell In Worksheets("Sheet1").Range("A1:A100")
    If oCell.Interior.ColorIndex = 3 Then
    If bNotFirst Then
    Union(Selection, oCell).Select
    Else
    oCell.Select
    bNotFirst = True
    End If
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    Legare!!! Wonderful!
    Works like a charm Thanx but dont understand the code--maybe a few comments would help if u have the patience!!! why "notfirst" for instance???
    Thanx
    Smbs

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    bNotFirst is there so that the first cell found that matches the color will be just selected and not added to the current selection which may or may not be cells of the right color. After the first cell is selected, then bNotFirst is set to True and all other cells that match the color are added to the current selection using the Union function.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    Understood great
    Thanx
    Smbs

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    Legare!!
    If I would to select by type of font ie "arial" or "david"
    why doesnt this work
    This is substituted into your original code
    Public Sub SelectRed()
    Dim bNotFirst As Boolean
    Dim oCell As Range
    bNotFirst = False
    For Each oCell In Worksheets("Sheet1").Range("A1:A100")
    If oCell.Font.Name = "David" Then "THIS DOES NOT WORK" HOW IS IT DONE???
    "etc" as per your original code
    Thanx again I never stop learning
    Smbs

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    I think you need to get the fontname excactly correct, and it is case sensitive. The best way to get the font name is to record a macro that selects the font you are interested in and use the name exactly as recorded.

    Also your code will fail if the font you are looking for is set as a result of conditional formatting.

    See if that helps.

    Andrew C

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    I copied that If statement out of your message and pasted it into my macro. I replaced "David" with "Arial" since I don't have a font named "David" and it worked fine for me. My only guess is that you don't have the font named spelled EXACTLY correctly, including the correct case.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select cells by color (excel97 +)

    Thanx ---works fine as u both say it is case sensitive the vba editor does not capitalize because of the "quotes" --- I pressume.
    Thanx
    SMBS

Posting Permissions

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