Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    i need a mcro to rdering by color line (2000)

    I have a sheet with many data in text format, many of this line are colored in RED an many in BLUE the remain in the black. The range is A3:R65000, with a macro i make an symple ordering in this mode:
    order 1) all line in RED
    order 2) all line in BLUE
    order 3) the remain line in BLACK

    My problem is:

    Order in the same color range from the column F (start to F3) is present a DATE dd/mm/yyyy

    Explain:
    after to run my macro i have many line in RED many line in BLUE and mnay in BALCK

    The request macro it must:

    order the first block of lines in RED from indexed column F

    order the second block of lines in BLUE from indexed column F

    order the third block of lines in BLACK from indexed column F

    I hope of to have been clear...

    Note: i dont know the number of line colored this can change

    final result is in the attache

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

    Re: i need a mcro to rdering by color line (2000)

    Hello Salvatore,

    The following function from Chip Pearson can be used on the worksheet to return the color index of the font in a cell:

    Function CellColorIndex(InRange As Range, Optional _
    OfText As Boolean = False) As Integer
    ' This function returns the ColorIndex value of a the Interior
    ' (background) of a cell, or, if OfText is true, of the Font in the cell.
    Application.Volatile True
    If OfText = True Then
    CellColorIndex = InRange(1, 1).Font.ColorIndex
    Else
    CellColorIndex = InRange(1, 1).Interior.ColorIndex
    End If
    End Function

    In your spreadsheet, you have RED = 3, BLUE = 5 and AUTOMATIC (BLACK) = -4105. If you take the absolute value of this, you can order on it. In cell S3, enter this formula:

    =ABS(CellColorIndex(A3,TRUE))

    and fill down as far as needed. You can now select Data | Sort... and sort on column S, then column F. This will sort by color first, and by date within a color. If you like, you can hide column S.

    See the attached modified version of your spreadsheet.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: i need a mcro to rdering by color line (2000)

    TKS VERY TKS FOR THE PRIFESSIONAL EXPLAIN!
    Sal

Posting Permissions

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