Hi Lon,
If you have totals you are somehow keeping them out of the sort.

If you have an indicator on the row or if not make a flag then include that flag in
your conditional formatting to ignore formatting with that condition. then you
can format the entire column(s).

=AND(M8="", ISBLANK(G8)=FALSE, HasComment(G8)=FALSE,OR(G8/AVERAGE(C8:E8)<1-$C$43,
G8/AVERAGE(C8:E8)1+$C$43))
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lon Sarnoff" wrote in message ...
The conditional formatting formulae take the following form:

=AND(ISBLANK(G8)=FALSE,hascomment(G8)=FALSE,OR(G8/AVERAGE(C8:E8)<1-$C$43,
G8/AVERAGE(C8:E8)1+$C$43))

The formatting applied is a change of the cells color.

In the formula, "hascomment" is a user defined function. Not all the cells,
however, have the same formula. The cells in some rows do not have
conditional formatting, and serve as summaries of certain rows above. The
problem after the sort is that some of the data to which the conditional
formatting should be applied end up in the positions of the summarizing rows,
and vice versa.

Recognizing the impact of the sort upon the summarizing rows, before the
sort I substitute the cells' values for their formulas. I had been hoping
that the conditional formatting would migrate with those values.

--
Lon Sarnoff


"David McRitchie" wrote:

Hi Lon,
Conditional Formatting is like regular cell formatting in being attached to cells.

Sorting, Some notes on sorting in Excel
http://www.mvps.org/dmcritchie/excel...ng.htm#borders

Shows an example of the shading traveling with the cell but not the borders.
The borders can't travel because borders are shared between cells.

So what is involved with your Conditional Formatting. Mostly what I have
for Conditional Formatting is applied to entire columns. So migration or lack
of it is not a problem for me, plus I don't often sort such lists. What do your
formulas look like for the C.F. and how were they applied.
entire column(s)
copying formatting of a range of cells
individual cells formatted
what is involved
fonts: color, size, weight (bold), italic
borders
shading and patterns

nothing yet related to your problem on my Conditional Formatting page
that I am aware of unless
http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lon Sarnoff" wrote in message ...
My spreadsheet contains a variety of conditional formats. It appears that
those formats are not "travelling" with their cell values when the a range is
sorted by the code shown below, and so the newly positioned data values have
incorrect conditions applied to determine their new formats. What am I
missing? (I am using Excel 2003.)

Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

--
Lon Sarnoff