Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Thanked 0 Times in 0 Posts

    Sorting (Excel 97 SR@)

    Hello! I don't have any clue if this is possible but I am in need of a way to sort data but highlight the data that belongs to a certain property. I have attached a simplified worksheet to better explain what I am looking for. I think it can be accomplished with a pivot table and possibly conditional formatting but I am not sure. Currently I am using a macro to accomplish this and it works but it is cumbersome to edit each time I need to add a new row item.

    Any help or suggestions would be greatly appreciated.
    Attached Files Attached Files

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

    Re: Sorting (Excel 97 SR@)

    How about the macro below. It will set the font color of the values in the report to be the same as the font color of the store name in the data sheet. It creates the report on Sheet2, but that can easily be moved to Sheet1 if that is where you want it. The macro should not need to be changed if the number of rows or columns in the data sheet changes.

    <pre>Public Sub CreateReport()
    Dim I As Long, J As Long
    Dim oCell As Range
    I = 0
    While Worksheets("Sheet1").Range("A3").Offset(I, 0) <> ""
    Worksheets("Sheet2").Range("A2").Offset(0, I) = Worksheets("Sheet1").Range("A3").Offset(I, 0)
    J = 0
    While Worksheets("Sheet1").Range("B3").Offset(I, J) <> ""
    Worksheets("Sheet2").Range("A3").Offset(J, I) = Worksheets("Sheet1").Range("B3").Offset(I, J)
    Worksheets("Sheet2").Range("A3").Offset(J, I).Font.ColorIndex = _
    Worksheets("Sheet1").Range("B2").Offset(0, J).Font.ColorIndex
    J = J + 1
    Range(Worksheets("Sheet2").Range("A3").Offset(0, I), Worksheets("Sheet2").Range("A3").Offset(J - 1, I)).Sort _
    Key1:=Worksheets("Sheet2").Range("A3").Offset(0, I), Order1:=xlAscending
    I = I + 1
    End Sub

    I have attached your workbook with an example of the macro.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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