Sorting: Keep cell formatting

[localfiend]

Any way to get the formatting to stay with a row that has just been sorted (by formatting I mean cell shading and borders)?

I have a page full of information that will be updated periodically and needs to be sorted. However, I would like to be able to keep the cell shading & borders with the cell info it was originally used for.

 

[Jim-in-I]

When you are sorting, are you selecting all the columns of data or just specific columns in the range?

If you sort all the columns, the row formatting will follow. If you select individual columns and sort, the formatting will stay with the cells in that column, but will be out of alignment with the other formatted rows.

Two possible solutions:

1) Select all columns and rows of values when you sort. - I would think you would want to do this, if your rows have associated data that should stay in the same row together.

2) Use Conditional Formatting to apply a certain border or shade. Then sorting will not effect the formats.

Post back if there is more to the issue than I have guessed.
Jim

 

[gardnertoo]

I'm imagining something like this....a sheet is formatted with every other row shaded gray. After sorting, you get some gray rows bunched together and some unshaded rows bunched together. What you want is for the DATA to move, but the SHADING to stay where it was. Is that right, localfiend?

(Not that I can do that for you; I just want to get the problem stated clearly so somebody else can get you where you want)

 

[Jim-in-I]

Good point Gardnertoo, I had not envisioned that scenario.

If your row formatting issue is as Gardnertoo describes, you can use Conditional Formatting to ensure every other row is shaded.

• Select the entire range that you want to have shaded
• Select Format menu -> Conditional Formatting...
• In the Conditional Formatting dialog box, select the “Formula Is” option from the drop-down box and enter this formula: =MOD(ROW(),2)=0.
• Click the Format option and select the Patterns tab
• Select the shading color of your choice
• Click OK until you finish

Now the row shading will remain consistent, even when you insert, delete or sort rows.

Hope this helps,
Jim

 

[localfiend]

Well, I've got Vista x64 and Office 2007 so my attempts at using the HTML maker to show my sheet have failed so heres a picture instead.

The sheet before sorting:

I run my sort code:

    Range("A6:M97").Select
    ActiveWorkbook.Worksheets("EXPERIMENTAL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("EXPERIMENTAL").Sort.SortFields.Add Key:=Range("A6:A97"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("EXPERIMENTAL").Sort.SortFields.Add Key:=Range("B6:B97"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("EXPERIMENTAL").Sort
        .SetRange Range("A6:M97")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

And here's the outcome:

All of the cell shading follows with the rows as it should. However - all of the cell borders stay exactly where they were while the "text" moves without it. What I'm after is a way to move the borders along with my "totals" rows.

 

[localfiend]

Bump for the new week.

 

[localfiend]

bump

 

[wprager]

Hmm, mine seems to keep the formatting intact. I didn't find any Tools->Options setting for this. Could it be version-specific (I've got Excel 2003).

 

[Norie]

Did you see the comment regarding conditional formatting?

As far as I can see the cells you want the borders on in cell A are those with Totals/Averages and Costs.

CF could easily be set up for that.

 

[localfiend]

I can't find any options for it either and it just seems weird that the cell shading will change rows properly but the borders wont. I suppose there's probably some way to make excel manually draw all the boxes for me - but that would make what I want to do overly complicated. I imagine its probably something simple that is eluding me.