Conditional formatting does not follow value when list is sorted
 

I have a set of dates and times in a schedule that are compared to holidays
and conditionally formatted (changes text color) if the date is a holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting (holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as they
should be but the fromatting stays in its original cell and does not move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell value?

Conditional formatting does not follow value when list is sorted
 

Might the problem be that you've used absolute, rather than relative,
addressing?
What happens if you change your CF formula from =$Q$7=TRUE to =Q7=TRUE, or
even just to =Q7 ?
It also isn't clear how you've got your data laid out, and whether you
sorted your intermediate cells (like Q7) with the corresponding data.
If you look in the CF formula for a particular cell, it should tell you
which cell it is looking at to determine the format.
--
David Biddulph

"rtbrd" wrote in message
...
I have a set of dates and times in a schedule that are compared to holidays
and conditionally formatted (changes text color) if the date is a holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting (holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as they
should be but the fromatting stays in its original cell and does not move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell value?

Conditional formatting does not follow value when list is sort
 

David,

The spread sheet is much more complex than I mentioned below, too much to
try and explain here. "Q7" is actually a range of 4 cells across, followed
by 4 blank rows, this being one group, and is 9 groups deep. There are 32 of
these groupings in total.

The CF does tell me the cell it is looking at, unfortunately it is going to
format the particular cell that contains the CF function, let's say cell
D100. When I sort the data the value in cell D100 moves to cell D99 and the
value in cell D99 moves to cell D100. The original value in D100 is the
value that sould be conditionally formatted but now resides in cell D99. The
new sorted value (not the one that should be formatted) is now in cell D100
and gets the formatting. Is there any way to cause the formatting to move
with the sort?

"David Biddulph" wrote:

Might the problem be that you've used absolute, rather than relative,
addressing?
What happens if you change your CF formula from =$Q$7=TRUE to =Q7=TRUE, or
even just to =Q7 ?
It also isn't clear how you've got your data laid out, and whether you
sorted your intermediate cells (like Q7) with the corresponding data.
If you look in the CF formula for a particular cell, it should tell you
which cell it is looking at to determine the format.
--
David Biddulph

"rtbrd" wrote in message
...
I have a set of dates and times in a schedule that are compared to holidays
and conditionally formatted (changes text color) if the date is a holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting (holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as they
should be but the fromatting stays in its original cell and does not move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell value?

Conditional formatting does not follow value when list is sort
 

You ask:
"Is there any way to cause the formatting to move with the sort?"

My suggestions were in my previous message:
Use relative rather than abolute addressing.
Ensure that when you are sorting your data cells you sort the associated
cells (including those which affect the CF condition) with them. Select the
relevant range of rows and column before you sort. If the cells affecting
the CF aren't structured the same as structure of the basic data (such as
not having a one row to one row mapping), then you'll need to restructure
the data before you sort.
--
David Biddulph

"rtbrd" wrote in message
...
David,

The spread sheet is much more complex than I mentioned below, too much to
try and explain here. "Q7" is actually a range of 4 cells across,
followed
by 4 blank rows, this being one group, and is 9 groups deep. There are 32
of
these groupings in total.

The CF does tell me the cell it is looking at, unfortunately it is going
to
format the particular cell that contains the CF function, let's say cell
D100. When I sort the data the value in cell D100 moves to cell D99 and
the
value in cell D99 moves to cell D100. The original value in D100 is the
value that sould be conditionally formatted but now resides in cell D99.
The
new sorted value (not the one that should be formatted) is now in cell
D100
and gets the formatting. Is there any way to cause the formatting to move
with the sort?

"David Biddulph" wrote:

Might the problem be that you've used absolute, rather than relative,
addressing?
What happens if you change your CF formula from =$Q$7=TRUE to =Q7=TRUE,
or
even just to =Q7 ?
It also isn't clear how you've got your data laid out, and whether you
sorted your intermediate cells (like Q7) with the corresponding data.
If you look in the CF formula for a particular cell, it should tell you
which cell it is looking at to determine the format.
--
David Biddulph

"rtbrd" wrote in message
...
I have a set of dates and times in a schedule that are compared to
holidays
and conditionally formatted (changes text color) if the date is a
holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting
(holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as
they
should be but the fromatting stays in its original cell and does not
move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell
value?

Conditional formatting does not follow value when list is sort
 

David,

I copied my decision matrix down and it now resides next to the data I want
to sort and this will work.

Thanx

"David Biddulph" wrote:

You ask:
"Is there any way to cause the formatting to move with the sort?"

My suggestions were in my previous message:
Use relative rather than abolute addressing.
Ensure that when you are sorting your data cells you sort the associated
cells (including those which affect the CF condition) with them. Select the
relevant range of rows and column before you sort. If the cells affecting
the CF aren't structured the same as structure of the basic data (such as
not having a one row to one row mapping), then you'll need to restructure
the data before you sort.
--
David Biddulph

"rtbrd" wrote in message
...
David,

The spread sheet is much more complex than I mentioned below, too much to
try and explain here. "Q7" is actually a range of 4 cells across,
followed
by 4 blank rows, this being one group, and is 9 groups deep. There are 32
of
these groupings in total.

The CF does tell me the cell it is looking at, unfortunately it is going
to
format the particular cell that contains the CF function, let's say cell
D100. When I sort the data the value in cell D100 moves to cell D99 and
the
value in cell D99 moves to cell D100. The original value in D100 is the
value that sould be conditionally formatted but now resides in cell D99.
The
new sorted value (not the one that should be formatted) is now in cell
D100
and gets the formatting. Is there any way to cause the formatting to move
with the sort?

"David Biddulph" wrote:

Might the problem be that you've used absolute, rather than relative,
addressing?
What happens if you change your CF formula from =$Q$7=TRUE to =Q7=TRUE,
or
even just to =Q7 ?
It also isn't clear how you've got your data laid out, and whether you
sorted your intermediate cells (like Q7) with the corresponding data.
If you look in the CF formula for a particular cell, it should tell you
which cell it is looking at to determine the format.
--
David Biddulph

"rtbrd" wrote in message
...
I have a set of dates and times in a schedule that are compared to
holidays
and conditionally formatted (changes text color) if the date is a
holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting
(holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as
they
should be but the fromatting stays in its original cell and does not
move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell
value?