Conditional formatting not getting sorted

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

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" <[email protected]> wrote in message news:[email protected]...
> 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

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" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> > > 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
> >
> >
> >

Thanks for the suggestions. As I mentioned, however, the problem is in both
directions, i.e., cells which are formatted and shouldn't be, and cells which
are not formatted and should be.

I have found a somewhat cumbersome work-around: parsing the cells before
sort, creating separate copies and then rejoining them afterwards. I had
been hoping that I was misunderstanding the migration issue, and that there
would have been an easier remedy.

Thanks
--
Lon Sarnoff


"David McRitchie" wrote:

> 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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

Hi Lon,
My suggestion was to identify within the Conditional Formatting Formula
whether or not the formula should be applied or not, so that you can use
the C.F down entire columns.

You have not indicated any reason why you can't do that..

Obviously you can look at the sheet and tell that the
formatting should not be applied to a cell, if you specify that within the
formula then your problem is solved. That is why I suggested using an
additional check in or with AND to add additional nesting levels.

You can visually test the formula by placing it into a help column to see
if it produces True or False.
---
David McRitchie

"Lon Sarnoff" <[email protected]> wrote in ...
> Thanks for the suggestions. As I mentioned, however, the problem is in both
> directions, i.e., cells which are formatted and shouldn't be, and cells which
> are not formatted and should be.
>
> I have found a somewhat cumbersome work-around: parsing the cells before
> sort, creating separate copies and then rejoining them afterwards. I had
> been hoping that I was misunderstanding the migration issue, and that there
> would have been an easier remedy.
>
> Thanks
> --
> Lon Sarnoff
>
>
> "David McRitchie" wrote:
>
> > 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" <[email protected]> wrote in message news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >