Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting not getting sorted
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting not getting sorted
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting not getting sorted
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting not getting sorted
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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting not getting sorted
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" 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First Value in Sorted Column is Not Sorted Properly | Excel Discussion (Misc queries) | |||
Conditional formatting does not follow value when list is sorted | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting - Drawing Lines Between Sorted Groups | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |