Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting color cells in Excel 2003

    I have list of dates (small sample attached) Some of the cells are colored. I would like to extract only colored cells and list them to another location (either on same Sheet ot Sheet2) The list in the new location in not necessary to be also colored- if colored-OK).
    Since the dates are highlighted based on Conditional Formattig, in other situation color cells will be different,
    So, is there any general formula or macro enablig extracting the color cells (only them) and list to a new location?
    Thanks in advanced for your help.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The easiset way is to add a column with the formula that cond formatting uses and filter that for true then copy the filtered list.

    Testing for conditional formatting can be very complicated see Chip Pearsons UDFs at http://www.cpearson.com/excel/cfcolors.htm for some examples if you want to go this route with pre-XL2007 cond formatting.
    Steve

    PS Here is some code that gets the cell color, whether CF or not:
    http://www.excelfox.com/forum/f22/ge...ng-or-not-338/

    in XL2010 you can use something like:
    range("A3").DisplayFormat.Interior.Colorindex
    or
    range("A3").DisplayFormat.Interior.Color

    to get the displayed color (CF or explicit)

    Steve
    Last edited by sdckapr; 2014-03-13 at 13:10.

  3. #3
    New Lounger
    Join Date
    Mar 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for reply
    I know that is complicated. And because of this I asked specialists in this forum. I hardly believe that no macro could be proposed or written for this case. As people say:"Everything is possible" Sorry for this saying, but I really rely on your experience since my knowledge is not that much. Anyhow, I'm waiting some other ideas and proposals. Thanks again.
    P.S. What about this column. Pls give me more details

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you are looking for more specific coding than the general explanations I provided, you will need to provide more details about exactly what the want the code to do and how different the real data will be from the sample.

    Here is what I envisioned with the extra column. It is shown after it has been filtered so this range can be copied to a new location.

    Steve
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Mar 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the fast reply. This could work for me but it is almost the same if I convert months to numbers with formula =TEXT(A2;"m") and the sorted by months. The thing I would like to get is to extract i separate list all colored cells. It is shown in the attachment. I do hope that will be some solution.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    What do you not like about the solution I proposed with the extra column and filtering. This is the easiest and fastest solution.

    What am I missing?

    Steve

  7. #7
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,348
    Thanks
    48
    Thanked 271 Times in 250 Posts
    Grey-hair,

    Instead of filtering by the colored cells, can you filter by the condition itself if it were true? If the condition is true, then it would also be colored.

    Code:
    Public Sub FilterByCondition()
    Row = 2
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To Lastrow
        If Month(Cells(I, 1)) = Month(Date) Then
            Cells(Row, 4) = Cells(I, 1)
            Row = Row + 1
        End If
    Next I
    End Sub
    Attached Files Attached Files

Posting Permissions

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