Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clear cell if its colorindex = # (excel 2000)

    Hi, I am wondering what kind of selection on a worksheet i would use to clear all cells that are the color

    .ColorIndex = 36

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    Add this to a module, select the range to check and run the macro

    <pre>Option Explicit
    Sub ClearColorIndex()
    Dim rCell As Range
    For Each rCell In Selection
    If rCell.Interior.ColorIndex = 36 Then
    rCell.Clear
    End If
    Next
    End Sub</pre>


    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    You could use the following VBA code:

    Sub ClearCellsWithColorIndex(n As Integer)
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If oCell.Interior.ColorIndex = n Then
    oCell.ClearContents
    End If
    Next oCell
    End Sub

    Sub Test()
    ClearCellsWithColorIndex 36
    End Sub

    The ClearCellsWithColorIndex procedure will clear the contents of all cells whose interior has the specified colorindex. If you want to clear the formatting too, change

    oCell.ClearContents

    to

    oCell.Clear

    The macro Test uses this procedure to clear (the contents of) all cells whose interior has colorindex 36.

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    Thanks Steve,

    This will remove the color though,

    what I have is a a worksheet that some one fllls in like a form, Yellow cells are what they fill in. I want to have a button that can clear all the yellows cells.

    thanks for the speed of your reply

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    is it possible to select all yellow cells first then ClearContents?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    Why?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    Change
    rCell.clear

    to
    rCell.ClearContents

    Steve

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    it might be done faster??

    It seems i can not attach your code behind a button??

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    ignore the part about not attaching to a button, i realized your example was a function, I have t working [img]/forums/images/smilies/smile.gif[/img]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    Selecting cells is usually not very fast. And you'd still have to loop through the cells in order to select the ones with colorindex 36.

    The ClearCellsWithColorIndex procedure is not a macro, since it has an argument. So it can't be assigned to a button.
    But you should be able to assign the Test macro (or whatever you want to name it) to a button.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    <pre>Option Explicit
    Sub ClearColorIndex()
    Dim rCell As Range
    Dim rSelect As Range
    For Each rCell In Selection
    If rCell.Interior.ColorIndex = 36 Then
    If rSelect Is Nothing Then
    Set rSelect = rCell
    Else
    Set rSelect = Union(rSelect, rCell)
    End If
    End If
    Next
    rSelect.Select
    rSelect.ClearContents
    Set rCell = Nothing
    Set rSelect = Nothing
    End Sub</pre>


    I don't think selecting will be any faster...

    If this is a set range, why not just name it: (select all the colored cells and then enter a name in the "NameBox", eg, ColorIndex36)

    Then your code just needs to clear the contents of the name:
    Sub ClearColorIndex36()
    Range("ColorIndex36").clearcontents
    End Sub

    Steve

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    ok, so because it will need to loop through each cell a select is useless? I was thinking that it be as simple as selecting it all with my mouse and hitting delete

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    If you're willing to select the cells manually, do so by all means. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    unfortunatly the cells that are yellow are every where there would be about 20 named ranges. I think i ll have to live with the individual cell clearcontents, it works just fine.

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear cell if its colorindex = # (excel 2000)

    nope i like the automatic way, thanks for your Help!!! steve and Hans!!! <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

Page 1 of 2 12 LastLast

Posting Permissions

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