Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Excel Experts,

    I have a sheet that has 3000 lines of data. some of the cells have been filled with colour. I need to either sort them so that i have all the coloured cells together or Find and replace all the non colour cells with Blank.

    Is there a way of doing this?

    Hoping to find a simple solution for this.

    Thanks
    Baiju

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Baiju G Nath' post='791790' date='02-Sep-2009 16:39']Hi Excel Experts,

    I have a sheet that has 3000 lines of data. some of the cells have been filled with colour. I need to either sort them so that i have all the coloured cells together or Find and replace all the non colour cells with Blank.

    Is there a way of doing this?

    Hoping to find a simple solution for this.

    Thanks
    Baiju[/quote]
    AFAIK, Excel has no in built support to sort/fine data depend on cell color. This can be done either with VBA or you can use modColorFunctions Add-in created by Chip Pearson.
    Attached Files Attached Files
    Regards
    Prasad

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if the cells have been manually colored - that is the coloring is not the result of conditional formatting - highlight the cells you want to examine and run the following macro:

    Code:
    Sub RemoveFromColor()
    Dim rngCell As Range
    For Each rngCell In Selection
    	If rngCell.Interior.ColorIndex < 0 Then
    		rngCell.ClearContents
    	End If
    Next
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    HI Prasad,

    Can you let me know how to use this Add-ins

    Regards
    Baiju

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Baiju G Nath' post='791930' date='03-Sep-2009 11:29']HI Prasad,

    Can you let me know how to use this Add-ins

    Regards
    Baiju[/quote]
    Close all excel applications. If using a LAN, save the add-in to Documents & Settings -->user name -->Application Data -->Microsoft -->Addins else Program Files --> Microsoft Office --> Addins. Open Excel and check the Modcolorfunctions box under Tools --> Add-Ins. Addin is now ready to use.

    In A1, color the cell & in B2 write the formula =colorindexofonecell(A1,0,0). This wil return the color index of cell interior. If you want to know the color index of text, simply change the formula to =colorindexofonecell(A1,1,0).

    [attachment=85381:untitled.JPG]
    [attachment=85382:untitled_1.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you are using Excel 2007 you can filter by colour, but not in previous versions.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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