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.

 

[localfiend]

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.

I could mess around with it and see how things go. It was mostly hoping to figure out the right way of getting this to work (I know, right is probably however you can get it to work)

I'll mess with conditional formatting, the only problem is that there are 8 totals rows, and they all say different things. Its not simply "Total" ect...

 

[Norie]

Yes, I know the all say different things.

But they do seem to have a little something in common, the all say Totals etc.

 

[wprager]

Where's my post? I made a post from home and can't find it now. Oh, well, here's what I previously said:

I could not see the pics of the spreadsheet at work (using IE) but saw them at home (using Firefox). I noticed that there are quite a few empty rows interspersed between the rows of data. Yet your sort range seems to include them all. Is there some option I don't know about that lets you leave empty row in their place when sorting? There is an xlSortOn property which I don't have in my version (2003), so maybe this is something new in 2007?

Anyhoew, could the problem be that the borders are not where you think they are? For example (going from memory here because I can't see the pictures), there are thick borders around rows 35 and 36 in the original, and they are missing from the rows where the data moved in the second picture. However if you look closely, there are empty rows above and below 35/36. Could it be that what you took to be a thick upper border for 35 is actually a thick lower border for 34? And similarly for the other rows.

 

[localfiend]

Where's my post? I made a post from home and can't find it now. Oh, well, here's what I previously said:

I could not see the pics of the spreadsheet at work (using IE) but saw them at home (using Firefox). I noticed that there are quite a few empty rows interspersed between the rows of data. Yet your sort range seems to include them all. Is there some option I don't know about that lets you leave empty row in their place when sorting? There is an xlSortOn property which I don't have in my version (2003), so maybe this is something new in 2007?

I was tricksy - columns A and B contain a bunch of values derived from another sheet that determin how the rows are sorted. All of the empty rows have hidden values in columns A and B as well to make sure that they end up in the right spots.

Anyhoew, could the problem be that the borders are not where you think they are? For example (going from memory here because I can't see the pictures), there are thick borders around rows 35 and 36 in the original, and they are missing from the rows where the data moved in the second picture. However if you look closely, there are empty rows above and below 35/36. Could it be that what you took to be a thick upper border for 35 is actually a thick lower border for 34? And similarly for the other rows.

That was the first thing I thought about trying. So I removed all of them and made sure that the borders top, bottom, and sides were made for the rows that are going to be moved.


I was able to use conditional formatting to add borders to the rows I wanted. However, it won't let me use thick borders - just the standard thin lines. Is there some option I don't know about that will give me more options? This whole thing has got me feeling rather silly.

 

[Norie]

If this is just for cosmetic purposes why not try some of the other formats.

eg red font and border

 

[localfiend]

If this is just for cosmetic purposes why not try some of the other formats.

eg red font and border

Oh I'm aware that there are all kinds of other things I could do to make my report look differently but this is how I want to do it. (boy do I sound stubborn) This formatting is one of the easiest to read wether its black & white, color, or being faxed. I simply assumed this might be an easy fix - which is primarily why I have not yet considered changing how everything looks.

 

[wprager]

Apparently you cannot use thick borders because conditional formatting won't do anything that alters the row/column size (that explains why I couldn't change the font size either).

 

[wprager]

Well, if you can do it with conditional formatting, then you could always write a macro to do it with VBA, and then you can simply re-apply the formatting as you need following the sort.

I know that still doesn't explain what is happening and why. Sorry.

 

[Norie]

It's not easy, in fact without code it's impossible.

 

[wprager]

I'm really puzzled by why I can't see the embedded images at work. I fired up IE at home, just now, and the images come through fine. At work -- nothing.

For example, localfiend's post with the sentence "This whole thing has got me feeling rather silly" has an image immediately following that sentence. I don't see it at work. Is there something special about these images? We filter a lot of stuff out at work but mostly it's stopping you from going somewhere, not selective removing certain items from a displayed web page.